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. - Private Sub DomesticQueryRun2_Click()
-
-
Dim db As Database
-
Dim Lrs As DAO.Recordset
-
Dim LSQL As String
-
Dim LTrackNum
-
-
'Open connection to current Access database
-
Set db = CurrentDb()
-
-
If Len(Forms![test]![TrkNbr]) > 0 Then
-
LTrackNum = "Y"
-
Else: LTrackNum = "N"
-
End If
-
-
If LTrackNum = "Y" Then
-
-
LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![CustNbr] & " OR Shp_trk_nbr = " & Forms![test]![TrkNbr]
-
-
Else: LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![CustNbr]
-
End If
-
-
Set Lrs = db.OpenRecordset(LSQL)
-
-
Lrs.Close
-
Set Lrs = Nothing
-
Set db = Nothing
-
-
-
End Sub
3 1284
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. - Cust_Nbr = '" & Forms![test]![CustNbr] & "'" OR Shp_trk_nbr = '" & Forms![test]![TrkNbr] & "'"
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. -
Private Sub DomesticQueryRun2_Click()
-
-
Dim db As Database
-
Dim Lrs As DAO.Recordset
-
Dim LSQL As String
-
Dim LTrackNum
-
-
'Open connection to current Access database
-
Set db = CurrentDb()
-
-
If Len(Forms![test]![TrkNbr]) > 0 Then
-
LTrackNum = "Y"
-
Else: LTrackNum = "N"
-
End If
-
-
If LTrackNum = "Y" Then
-
-
LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![CustNbr] & " OR Shp_trk_nbr = " & Forms![test]![TrkNbr]
-
-
Else: LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![CustNbr]
-
End If
-
-
Set Lrs = db.OpenRecordset(LSQL)
-
-
Lrs.Close
-
Set Lrs = Nothing
-
Set db = Nothing
-
-
-
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.: -
Private Sub DomesticQueryRun2_Click()
-
-
Dim db As Database
-
Dim Lrs As DAO.Recordset
-
Dim LSQL As String
- Dim Criteria As String
-
Dim LTrackNum
-
-
'Open connection to current Access database
-
Set db = CurrentDb()
-
-
'If Len(Forms![test]![TrkNbr]) > 0 Then
-
'LTrackNum = "Y"
-
'Else: LTrackNum = "N"
-
'End If
- LSQL = "Select * From CUSTOMERS WHERE "
- If IsNull(Me.trknbr) And IsNull(Me.custnbr) Then
-
'Show all records
-
LSQL = Left(LSQL, Len(LSQL) - 6) ' To strip off Where Statement
-
Else
- ' This section will build the criteria section of the SQL Statement that you're going to
-
' to be passing to the OpenRecordset. When the LSQL and Criteria Section are merged at the
-
' Bottom of the IF section, you can step through it and see how it works.
-
-
' I've also use the shortcut to refer to items on your form using me. syntax. This is alot
-
' shorter than using the Forms references that you were using before.
-
-
If Len(Me.trknbr) > 0 Then
-
If Len(Criteria) > 0 Then
-
Criteria = Criteria & "AND customer.shp_trk_nbr = " & Me.trknbr & vbCrLf
-
Else
-
Criteria = Criteria & "customer.shp_trk_nbr = " & Me.trknbr & vbCrLf
-
End If
-
End If
-
If Len(Me.custnbr) > 0 Then
-
If Len(Criteria) > 0 Then
-
Criteria = Criteria & "AND customer.Cust_Nbr = " & Me.custnbr & vbCrLf
-
Else
-
Criteria = Critiera & "customer.Cust_NBR = " & Me.custnbr & vbCrLf
-
End If
-
End If
-
End If
-
-
LSQL = LSQL & Criteria
-
-
- 'If LTrackNum = "Y" Then
-
-
'LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![custnbr] & " OR Shp_trk_nbr = " & Forms![test]![trknbr]
-
-
'Else: LSQL = "select * from customer WHERE customer.Cust_Nbr = " & Forms![test]![custnbr]
-
'End If
-
-
'create a ListControl and name it CustResults.
-
-
Then do the following
-
set me.custResult.recordset = db.OpenRecordset(LSQL,dbOpenSnapShot)
-
-
'This way you can have the results of your search display on your screen.
-
-
'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.
-
'Set Lrs = db.OpenRecordset(LSQL)
-
-
'Lrs.Close
-
'Set Lrs = Nothing
-
Set db = Nothing
-
-
-
End Sub
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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....
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
| |