List Box Search with Query Criteria!??

[james_IT]

Hi,

I am in the process of making a stock control system. One part of the database is customer details.

I have made a form so that we can search through the customer data. We want to search for records by entering First Name AND/OR SURNAME.

I have made the form with a list box in it that uses a query to search through the customer details table depending on values in a 'first name' and 'surname' text boxes. The code for the list box update/onchange looks like this -


Private Sub txtSearch_Change()
Dim vSearchString As String

vSearchString = txtSearch.Text
txtSearch2.Value = vSearchString
Me.List30.Requery

End Sub


Private Sub txtSearch3_Change()
Dim vSearchString As String

vSearchString = txtSearch3.Text
txtSearch4.Value = vSearchString
Me.List30.Requery

End Sub

There is therefore two crtieria in my query - one for first name -

Like "*" & [Forms]![CustomerSearch]![txtSearch2] & "*"

and one for surname -

Like "*" & [Forms]![CustomerSearch]![txtSearch4] & "*".


Now, i have three questions.

1) Firstly, how can i make it so that i can use Null values in one of the text boxes so i therefore can return just surname or just first name if i wish. Or is this to do with the query criteria not allowing null values?

2) secondly, how can i clear/reset everything so that whenever i open the form the text boxes are empty and the query has not searched anything.

3) I want to take the Customer ID number from that search (when double-clicked) and insert it into a text box in another form. What would the code for the event procedure be if the other form was called "Customer Purchases" and the text box was called "CustomerID".

Here is a copy of part of the Database, with the relevant parts.

PLEASE NOTE: THE DETAILS IN THIS EXAMPLE ARE NOT REAL. THE CUSTOMERS DO NOT REALLY EXIST.

http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=15250&stc=1&d=1161624371
Carpet Stock Control.zip



Please take into consideration that im a bit of a noob with this stuff so be gentle with your answers.

Thanks in advance for anyone's help, James

 

[jeremypaule]

Set your field values to NULL so theyll be blank, this code might help you with question #2

Private Sub butReset_Click()
Dim ctl
Dim sCtl As String

MANAGER = Null
MONTH = Null
YEAR = Null
VARIANCE = Null
CUSTOMERS = Null
COMMENTS = Null
Code = Null
ORIGIN = Null
DESTINATION = Null
Current = Null

DoCmd.Echo False
For Each ctl In Me.Controls
If ctl.Name Like "cbo*" Or ctl.Name Like "txt*" Then
ctl.Value = ctl.DefaultValue
End If
Next ctl
DoCmd.Echo True
End Sub

what you can do is Call butReset_Click and it'll reset the form.

For #3 you'll probably need to DLookup the value for Event Procedure of double click.

I could be wrong on some of this but I just want to try and help.

 

[james_IT]

thanks for your reply.

1) typing in null helps, but is there a way to either have "Null" already the default. Or it just expect that if nothing is typed then Null is used.

2) I understand the first pit of the code about use of a button to clear the form but i do not understand the second bit about how to set the query back/list box to display all the records again, like the beginning. Loop the onchange prodcendures?

3) will do

 

[james_IT]

thanks for your reply.

1) typing in null helps, but is there a way to either have "Null" already the default. Or it just expect that if nothing is typed then Null is used.

2) I understand the first pit of the code about use of a button to clear the form but i do not understand the second bit about how to set the query back/list box to display all the records again, like the beginning. Loop the on_change prodcedures?

3) will do

 

[james_IT]

thanks for your reply.

1) typing in null helps, but is there a way to either have "Null" already the default. Or it just expect that if nothing is typed then Null is used. Also if i have a clear button, it will clear the "Null" also.

2) I understand the first pit of the code about use of a button to clear the form but i do not understand the second bit about how to set the query back/list box to display all the records again, like the beginning. Loop the on_change prodcedures?

3) will do

 

[jeremypaule]

DoCmd.Echo False
For Each ctl In Me.Controls
If ctl.Name Like "cbo*" Or ctl.Name Like "txt*" Then
ctl.Value = ctl.DefaultValue
End If
Next ctl
DoCmd.Echo True
End Sub

assuming your using a textbox and combo boxes it will reset them back to the default value of NULL

 

[james_IT]

yes it does, but it does not reset the list box to display all of the records as in the beginning. Null must be entered manually before it displays all records.

 

[jeremypaule]

add lst box criteria to the IF statement.

 

[james_IT]

could you show me how the code should look. im not sure where to put it