Hi, for my course i am required to build a system for a small business, i have chosen a car dealership. I have made a search all stock form, so that a customer can fill it in to find whatever car it requires, e.g. automatic and petrol. I used an unbound form, but it only works if all fields are filled in. also the mileage and price need to be exact? how can i fix this
5 1173 jforbes 1,107
Recognized Expert Top Contributor
Here is a real world example: - Private Sub doRefresh()
-
-
Dim sSQL As String
-
-
Dim bDrawingBOM As Boolean
-
Dim bQuoteBOM As Boolean
-
Dim bQuoteLC As Boolean
-
-
Dim sSearchText As String
-
Dim sSupplier As String
-
Dim sComponent As String
-
Dim sQuote As String
-
Dim sDrawing As String
-
-
sSQL = ""
-
bDrawingBOM = Nz(Me.chkDrawingBOM.Value, True)
-
bQuoteBOM = Nz(Me.chkQuoteBOM.Value, True)
-
bQuoteLC = Nz(Me.chkQuoteLC.Value, True)
-
sSearchText = Nz(Me.txtSearchText.Value, "")
-
sSupplier = Nz(Me.cboSupplier.Value, "")
-
sComponent = Nz(Me.cboComponent.Value, "")
-
sQuote = Nz(Me.cboQuote.Value, "")
-
sDrawing = Nz(Me.cboDrawing.Value, "")
-
-
' Type Criteria
-
If Not (bDrawingBOM And bQuoteBOM And bQuoteLC) Then
-
If bDrawingBOM Then sSQL = sSQL & " OR (DocType='Drawing' AND LineType='BOM')"
-
If bQuoteBOM Then sSQL = sSQL & " OR (DocType='Quote' AND LineType='BOM')"
-
If bQuoteLC Then sSQL = sSQL & " OR (DocType='Quote' AND LineType='LC')"
-
sSQL = sSQL & " AND (" & Right(sSQL, Len(sSQL) - 4) & ") "
-
End If
-
-
' Text and ComboBoxes
-
If Len(sSearchText) > 0 Then
-
sSQL = sSQL & " AND ("
-
sSQL = sSQL & " DocNumber LIKE '*" & sSearchText & "*'"
-
sSQL = sSQL & " OR Description LIKE '*" & sSearchText & "*'"
-
sSQL = sSQL & " OR Supplier LIKE '*" & sSearchText & "*'"
-
sSQL = sSQL & " OR SupplierPartNumber LIKE '*" & sSearchText & "*'"
-
sSQL = sSQL & " )"
-
End If
-
If Len(sSupplier) > 0 Then sSQL = sSQL & " AND Supplier LIKE '*" & sSupplier & "*'"
-
If Len(sComponent) > 0 Then sSQL = sSQL & " AND Component LIKE '*" & sComponent & "*'"
-
If Len(sQuote) > 0 Then sSQL = sSQL & " AND DocNumber LIKE '*" & sQuote & "*'"
-
If Len(sDrawing) > 0 Then sSQL = sSQL & " AND DocNumber LIKE '*" & sDrawing & "*'"
-
-
' Build the SQL String
-
If Len(sSQL) > 5 Then
-
sSQL = "SELECT * FROM LineItem WHERE " & Right(sSQL, Len(sSQL) - 5)
-
Else
-
sSQL = "SELECT * FROM LineItem "
-
End If
-
If gDevEnvironment Then Debug.Print sSQL
-
Me.LineItemDatasheet.Form.RecordSource = sSQL
-
-
End Sub
It will create an output like this: - SELECT * FROM LineItem WHERE ((DocType='Drawing' AND LineType='BOM') OR (DocType='Quote' AND LineType='LC')) AND ( DocNumber LIKE '*test*' OR Description LIKE '*test*' OR Supplier LIKE '*test*' OR SupplierPartNumber LIKE '*test*' ) AND Supplier LIKE '*THERMAL*'
-
Or
-
SELECT * FROM LineItem WHERE ( DocNumber LIKE '*test*' OR Description LIKE '*test*' OR Supplier LIKE '*test*' OR SupplierPartNumber LIKE '*test*' ) AND DocNumber LIKE '*Quotevalue*'
Instead of setting the RecordSource, you could set the Form's Filter property. Just an option.
You can use Between to specify a mileage range.
Hi sorry, I'm only in 6th form, my coding is not very good. here is a screen shot of what I've done/Users/Tanvir/Desktop/Screen Shot 2015-01-27 at 19.19.47.png
this is the type of code i used [forms]![Search Stock Form]![Model] sorry the screenshot did not attach
jforbes 1,107
Recognized Expert Top Contributor
The idea is to build up a RecordSource or Filter based on what the user has filled out on the Form. By building it up based on what is supplied, the fields that don't have anything supplied for them are ignored and left out of the Where Clause for the SQL. There is another way to do which produces a somewhat static SQL but it is much more confusing to update, read, and explain.
Since when doing something like this, the fields are referenced more than once in Code, it is easier and possibly faster to load them into Variables. So in the following code, there are variables declared to hold the values of the Fields on the Form along with a String to use to build the SQL. Once the variables are declared, they are filled in with the values from the Form. The Nz() function will make sure we don't stuff a Null value into any of the Variables: - Dim sSQL As String
-
-
Dim bDrawingBOM As Boolean
-
Dim bQuoteBOM As Boolean
-
Dim bQuoteLC As Boolean
-
-
Dim sSearchText As String
-
Dim sSupplier As String
-
Dim sComponent As String
-
Dim sQuote As String
-
Dim sDrawing As String
-
-
sSQL = ""
-
bDrawingBOM = Nz(Me.chkDrawingBOM.Value, True)
-
bQuoteBOM = Nz(Me.chkQuoteBOM.Value, True)
-
bQuoteLC = Nz(Me.chkQuoteLC.Value, True)
-
sSearchText = Nz(Me.txtSearchText.Value, "")
-
sSupplier = Nz(Me.cboSupplier.Value, "")
-
sComponent = Nz(Me.cboComponent.Value, "")
-
sQuote = Nz(Me.cboQuote.Value, "")
-
sDrawing = Nz(Me.cboDrawing.Value, "")
-
...
Next, the code inspects the user supplied values and if there is something supplied, it adds the SQL to the SQL variable: - ' Type Criteria
-
If Not (bDrawingBOM And bQuoteBOM And bQuoteLC) Then
-
If bDrawingBOM Then sSQL = sSQL & " OR (DocType='Drawing' AND LineType='BOM')"
-
If bQuoteBOM Then sSQL = sSQL & " OR (DocType='Quote' AND LineType='BOM')"
-
If bQuoteLC Then sSQL = sSQL & " OR (DocType='Quote' AND LineType='LC')"
-
sSQL = " AND (" & Right(sSQL, Len(sSQL) - 4) & ") "
-
End If
-
-
' Text and ComboBoxes
-
If Len(sSearchText) > 0 Then
-
sSQL = sSQL & " AND ("
-
sSQL = sSQL & " DocNumber LIKE '*" & sSearchText & "*'"
-
sSQL = sSQL & " OR Description LIKE '*" & sSearchText & "*'"
-
sSQL = sSQL & " OR Supplier LIKE '*" & sSearchText & "*'"
-
sSQL = sSQL & " OR SupplierPartNumber LIKE '*" & sSearchText & "*'"
-
sSQL = sSQL & " )"
-
End If
-
If Len(sSupplier) > 0 Then sSQL = sSQL & " AND Supplier LIKE '*" & sSupplier & "*'"
-
If Len(sComponent) > 0 Then sSQL = sSQL & " AND Component LIKE '*" & sComponent & "*'"
-
If Len(sQuote) > 0 Then sSQL = sSQL & " AND DocNumber LIKE '*" & sQuote & "*'"
-
If Len(sDrawing) > 0 Then sSQL = sSQL & " AND DocNumber LIKE '*" & sDrawing & "*'"
When building the SQL String, the code prepends an " AND " to the SQL Variable no matter what. It will be clipped off right before the SQL Variable is used. It is just to include it at all times and assume it is there than to test for it over and over. This is where the prepended " AND " is removed: - ' Build the SQL String
-
If Len(sSQL) > 5 Then
-
sSQL = "SELECT * FROM LineItem WHERE " & Right(sSQL, Len(sSQL) - 5)
-
Else
-
sSQL = "SELECT * FROM LineItem "
-
End If
Lastly the SQL is used. Here a subForm named LineItemDatasheet has it's RecordSource set to the SQL Variable: - Me.LineItemDatasheet.Form.RecordSource = sSQL
-
zmbd 5,501
Recognized Expert Moderator Expert
TanvirShahjahan
According to the terms of use of the site, we do not normally answer homework/course-work questions.
jforbes has been very kind and given you a few very good answers/suggestions.
Should you have any further difficulty, we are going to need to see your work - quite simply, it's not fair to yourself nor to your classmates to have an expert(s) such as JF or myself do your assignment.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Tim Marshall |
last post by:
This is an embarrassing question to which I should know the answer, but
I am not 100% sure of myself. My applications, whether they are Jet or
Oracle usually deal with reporting on existing apps...
|
by: huela |
last post by:
Somehow i have to display the recordset in unbound form
When i use the following display the data in recordset to unbound
form.
I have a question:
i can only display the last record data, all...
|
by: Pierre |
last post by:
Hi all,
To ease load on a network i close automatically form open
with a timer reset by user actions.
If the time is expired i go through the collections
of form and table and close all those...
|
by: Stig |
last post by:
Hi,
Any help on this one will be greatly appreciated as I have spent too
long banging my head against the screen trying to get it sorted.
Basically I would like to have a select all records...
|
by: Dave G |
last post by:
I am writing a function to fill in the data in an unbound form.
I have a table with field names of 1, 2, 3 etc up to approx 100. I have
an unbound form with fields called 1, 2 3 etc.
I gave...
| |
by: RZ15 |
last post by:
Hi,
I have a form that opens a report. The form allows the user to pick a particular warehouse or supplier and an order to sort by. Here is the code for it:
Private Sub cmdOK_Click()
...
|
by: DeZZar |
last post by:
Hi all,
Unfortunately I am quite a novice with Access!! I've created a number
of data bases for my work however becuase my skills are limited to
really built in functionality and wizards my...
|
by: dizzydangler |
last post by:
Hi all,
I am a new Access user and just starting to get my head around some of the basic concepts, so please take it easy on me :) My company has been managing client records on excel, and I’m in...
|
by: hedges98 |
last post by:
I am struggling with something that is probably quite simple. When I close a form, if certain fields are left empty then I want a message to pop up to tell the user these fields are required and then...
|
by: drus |
last post by:
I have a questions database. When user is filling out the form, the following fields are required: Questions, Author, Type of Question and Answer selected. Answer is selected by clicking on the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |