By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,491 Members | 1,868 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,491 IT Pros & Developers. It's quick & easy.

Changing RecordSource in form

P: 3
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub List0_AfterUpdate()
  2.  
  3.     Dim strSource As String
  4.  
  5.     If List0 = "ID" Then
  6.     strSource = "SELECT * FROM tblCustomer WHERE (((tblCustomer.CustomerID = [Please enter Customer ID: ])));"
  7.     End If
  8.  
  9.     If List0 = "Surname" Then
  10.     strSource = "SELECT * FROM tblCustomer WHERE (((tblCustomer.Surname = [Please enter Customer's Surname ])));"
  11.     End If
  12.  
  13.     If List0 = "First Name" Then
  14.     strSource = "SELECT * FROM tblCustomer WHERE (((tblCustomer.FirstName = [Please enter Customer's First Name: ])));"
  15.     End If
  16.  
  17.    Forms_frmCustomerBasic.RecordSource = strSource
  18.  
  19. End Sub
However, It doesn't want to work, when I open the second form.
Dec 12 '11 #1
Share this Question
Share on Google+
5 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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?
Dec 12 '11 #2

P: 3
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.
Dec 12 '11 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Expand|Select|Wrap|Line Numbers
  1. Docmd.Open "frmCustomerBasic",,,,acHidden
  2. Forms!frmCustomerBasic.Recordsource=strSource
  3. 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.
Dec 12 '11 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Dec 12 '11 #5

P: 3
Thank you for response. I will try more with opening the form first.
Dec 12 '11 #6

Post your reply

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