Changing RecordSource in form

[Oscar M]

Hello,

I have two forms: frmDialogueBox and frmSearchCustomer.
In first one I've put in a List Box with three options: ID, First Name, Surname.
In second one I've all fields based on my Customer table, tblCustomer.

What I am trying to do is to allow the user to search for different records and display them in the second form based on the criteria chosen in first form.

I have tried to set up the VB code in the first form with the list box as follows:

Private Sub List0_AfterUpdate()
 
    Dim strSource As String
 
    If List0 = "ID" Then
    strSource = "SELECT * FROM tblCustomer WHERE (((tblCustomer.CustomerID = [Please enter Customer ID: ])));"
    End If
 
    If List0 = "Surname" Then
    strSource = "SELECT * FROM tblCustomer WHERE (((tblCustomer.Surname = [Please enter Customer's Surname ])));"
    End If
 
    If List0 = "First Name" Then
    strSource = "SELECT * FROM tblCustomer WHERE (((tblCustomer.FirstName = [Please enter Customer's First Name: ])));"
    End If
 
   Forms_frmCustomerBasic.RecordSource = strSource
 
End Sub

However, It doesn't want to work, when I open the second form.

[TheSmileyCoder]

When posting in a online forum, you need to realise that we can only act on what you tell us. Simply saying that your code "Does not want to work" is far from adequate.
Is it giving an error? If so what error. Is it showing to many records? To few records? No records at all?

[Oscar M]

Oh I'm sorry. I also noticed there's some inconsistency in my question: the form is called frmCustomerBasic.

I have set it up with RecordSource: tblCustomer initially and it has all the fields from that table in it.

Now as I select one of the options in the list box in first form and open the frmCustomerBasic, it doesn't ask me for e.g. Customer ID or Surname based on listbox, but it displays all the records based on tblCustomer. I'd assume it just did not change the RecordSource upon selecting from list box at all.

[TheSmileyCoder]

I have never tried to set the recordsource of a form which I did not allready have open. When I try setting it like you have, I get a fatal error in Ac2003 (Whole of access dies, which is actually quite rare for me), allthough it seems to work in AC2007.

I dont know which version of access your using. There are several options to get around setting it. One could be to open it hidden, then modify the recordsource, and then show it.

Docmd.Open "frmCustomerBasic",,,,acHidden
Forms!frmCustomerBasic.Recordsource=strSource
Forms!frmCustomerBasic.Visible=True

Modifying a forms recordsource while the form is open in anything but design mode will cause an automatic requery (And thus pause waiting for your input paramater). Upon entering the paramater the code will resume, making the form visible. One issue with this approach is that you have no nice way of handling a user putting crap input (or just a typo) into the inputbox. A more neat way might be to prompt the user using a inputbox, validate the info, and either open form, or ask user to retype input.

[TheSmileyCoder]

One more thing. Form_frmCustomerBasic is NOT the same as frmCustomerBasic. One is an object within the other, and to be honest I am not sure which is inside which.

[Oscar M]

Thank you for response. I will try more with opening the form first.