469,167 Members | 1,979 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

Changing RecordSource in form

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
5 2815
TheSmileyCoder
2,321 Expert Mod 2GB
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
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
2,321 Expert Mod 2GB
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
2,321 Expert Mod 2GB
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
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.

Similar topics

2 posts views Thread by ColinWard | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.