473,326 Members | 2,173 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

VBA forms/parameters No Errors; No Results

2
Hello, I am trying to create a form that has multiple parameters. A user needs to enter a parameter but if one is left blank that is okay.
Expand|Select|Wrap|Line Numbers
  1. Private Sub DomesticQueryRun2_Click()
  2.  
  3.     Dim db As Database
  4.     Dim Lrs As DAO.Recordset
  5.     Dim LSQL As String
  6.     Dim LTrackNum
  7.  
  8.     'Open connection to current Access database
  9.    Set db = CurrentDb()
  10.  
  11. If Len(Forms![test]![TrkNbr]) > 0 Then
  12.    LTrackNum = "Y"
  13.     Else: LTrackNum = "N"
  14.     End If
  15.  
  16. If LTrackNum = "Y" Then
  17.  
  18. LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![CustNbr] & " OR Shp_trk_nbr = " & Forms![test]![TrkNbr]
  19.  
  20. Else: LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![CustNbr]
  21. End If
  22.  
  23. Set Lrs = db.OpenRecordset(LSQL)
  24.  
  25. Lrs.Close
  26. Set Lrs = Nothing
  27. Set db = Nothing
  28.  
  29.  
  30. End Sub
Jul 29 '08 #1
3 1284
puppydogbuddy
1,923 Expert 1GB
You did not state what problem you are having with your code? If I had to guess, I would guess that either Cust_Nbr or Shp_trk_nbr are text data types requiring text syntax as shown below, not the numeric syntax you used.


Expand|Select|Wrap|Line Numbers
  1. Cust_Nbr = '" & Forms![test]![CustNbr] & "'" OR Shp_trk_nbr = '" & Forms![test]![TrkNbr] & "'"
Jul 30 '08 #2
PianoMan64
374 Expert 256MB
Hello, I am trying to create a form that has multiple parameters. A user needs to enter a parameter but if one is left blank that is okay.
Expand|Select|Wrap|Line Numbers
  1. Private Sub DomesticQueryRun2_Click()
  2.  
  3.     Dim db As Database
  4.     Dim Lrs As DAO.Recordset
  5.     Dim LSQL As String
  6.     Dim LTrackNum
  7.  
  8.     'Open connection to current Access database
  9.    Set db = CurrentDb()
  10.  
  11. If Len(Forms![test]![TrkNbr]) > 0 Then
  12.    LTrackNum = "Y"
  13.     Else: LTrackNum = "N"
  14.     End If
  15.  
  16. If LTrackNum = "Y" Then
  17.  
  18. LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![CustNbr] & " OR Shp_trk_nbr = " & Forms![test]![TrkNbr]
  19.  
  20. Else: LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![CustNbr]
  21. End If
  22.  
  23. Set Lrs = db.OpenRecordset(LSQL)
  24.  
  25. Lrs.Close
  26. Set Lrs = Nothing
  27. Set db = Nothing
  28.  
  29.  
  30. End Sub
Teddie:

I'm assuming what you're trying to do is have 2 text boxes on your form, and fill what which ever ones you want and click on some button to do a search? If so, then here's what you need to do in your code:

You have to build the LSQL variable with the items you need based on the fact if there is anything in them any or all the fields. i.e.:

Expand|Select|Wrap|Line Numbers
  1. Private Sub DomesticQueryRun2_Click()
  2.  
  3. Dim db As Database
  4. Dim Lrs As DAO.Recordset
  5. Dim LSQL As String
  6.  Dim Criteria As String
  7. Dim LTrackNum
  8.  
  9. 'Open connection to current Access database
  10. Set db = CurrentDb()
  11.  
  12. 'If Len(Forms![test]![TrkNbr]) > 0 Then
  13. 'LTrackNum = "Y"
  14. 'Else: LTrackNum = "N"
  15. 'End If
  16. LSQL = "Select * From CUSTOMERS WHERE "
  17. If IsNull(Me.trknbr) And IsNull(Me.custnbr) Then
  18.         'Show all records
  19.         LSQL = Left(LSQL, Len(LSQL) - 6) ' To strip off Where Statement
  20. Else
  21.  ' This section will build the criteria section of the SQL Statement that you're going to
  22. ' to be passing to the OpenRecordset. When the LSQL and Criteria Section are merged at the
  23. ' Bottom of the IF section, you can step through it and see how it works.
  24.  
  25. ' I've also use the shortcut to refer to items on your form using me. syntax. This is alot
  26. ' shorter than using the Forms references that you were using before.
  27.  
  28.         If Len(Me.trknbr) > 0 Then
  29.             If Len(Criteria) > 0 Then
  30.                 Criteria = Criteria & "AND customer.shp_trk_nbr = " & Me.trknbr & vbCrLf
  31.             Else
  32.                 Criteria = Criteria & "customer.shp_trk_nbr = " & Me.trknbr & vbCrLf
  33.             End If
  34.         End If
  35.         If Len(Me.custnbr) > 0 Then
  36.             If Len(Criteria) > 0 Then
  37.                 Criteria = Criteria & "AND customer.Cust_Nbr = " & Me.custnbr & vbCrLf
  38.             Else
  39.                 Criteria = Critiera & "customer.Cust_NBR = " & Me.custnbr & vbCrLf
  40.             End If
  41.         End If
  42. End If
  43.  
  44. LSQL = LSQL & Criteria
  45.  
  46.  
  47.     'If LTrackNum = "Y" Then
  48.  
  49. 'LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![custnbr] & " OR Shp_trk_nbr = " & Forms![test]![trknbr]
  50.  
  51. 'Else: LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![custnbr]
  52. 'End If
  53.  
  54. 'create a ListControl and name it CustResults.
  55.  
  56. Then do the following
  57. set me.custResult.recordset = db.OpenRecordset(LSQL,dbOpenSnapShot)
  58.  
  59. 'This way you can have the results of your search display on your screen.
  60.  
  61. 'Make sure to set the properties of the ListControl to allow for all the columns in the return result as well as setting the widths for each column. If you need help with that, let me know.
  62. 'Set Lrs = db.OpenRecordset(LSQL)
  63.  
  64. 'Lrs.Close
  65. 'Set Lrs = Nothing
  66. Set db = Nothing
  67.  
  68.  
  69. End Sub
Jul 30 '08 #3
Teddie
2
Thank you PianoMan. I used your code and am getting a "Compile error: Method or data memeber not found" on the line
Set Me.custResults.Recordset = db.OpenRecordset(LSQL, dbOpenSnapshot)
Any idea how to fix this?

Okay, after googling the above error I changed Me.custResults.Recordset to Me!custResults.Recordset and am now getting a Run-Time error '3061' Too few parameters. Expected 1.

Also, can you help me with setting the properties of the ListControl.

Thanks so much!! I've been pulling my hair out for the last week trying to get this to run.
Jul 30 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: polytimi8 | last post by:
Hello, I would like to know if it is possible to create a form in Access2000, which would function like a calendar for 8 operating rooms in hospital, showing which hours are those closed for a...
6
by: ALthePal | last post by:
Hi, I'm not sure if we are able to or even how to loop through the web forms in a VB.NET project during design time. In MSAccess we are able to go through the database -> forms collection and...
4
by: Luisa Lopes | last post by:
Dear colleagues: I read somewhere that you could obtain the results of a query as a form. I have design a database for my books and waht I would like is to obtain the results of my queries...
1
by: Raposa Velha | last post by:
Olá Luisa ;-) If I understood correctly, you would like to display the results of a query in a form. No problem, just go to the "Create form by using wizard" and at the first step just choose...
0
by: Andrew Dowding | last post by:
Hi Everybody, I have been looking at problems with my Windows Forms C# application and it's little Jet 4 (Access) database for the last few days. The Windows Forms app implements a facade and...
1
by: Johnny | last post by:
Hi, I'm building an engineering calculator. The calculator receives input parameters in main window form, and I would like the results to be presented on another form (pop-up window form), however,...
0
by: Anonieko Ramos | last post by:
ASP.NET Forms Authentication Best Practices Dr. Dobb's Journal February 2004 Protecting user information is critical By Douglas Reilly Douglas is the author of Designing Microsoft ASP.NET...
1
by: aleicaro | last post by:
Hi, I have a problem with db2 query... I access to my database via jdbc using com.ibm.db2.jcc.DB2Driver. I build my string query in a java class. I have some parameters in my query, but there are...
12
by: raylopez99 | last post by:
Keywords: scope resolution, passing classes between parent and child forms, parameter constructor method, normal constructor, default constructor, forward reference, sharing classes between forms....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.