473,322 Members | 1,347 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 3227
TheSmileyCoder
2,322 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,322 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,322 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

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

Similar topics

2
by: ColinWard | last post by:
Hi. I have a form which has as its recordsource an SQL string. The SQL String is as follows: SELECT * from CONTACTS where false. this ensures that there is no data loaded in the form when the...
32
by: deko | last post by:
I have a popup form with a textbox that is bound to a memo field. I've been warned about memo fields so I'm wondering if I should use this code. Is there any risk with changing the form's...
2
by: G Gerard | last post by:
Hello I have noticed that whenever I set a form's RecordSource property in code it launches the form's OnActivate event. In one of my application I set a form's RecordSource to zero lenght...
2
by: cek172 | last post by:
Is there any way to change the recordsource of a subform from the mainform? I have a subform that I would like to have return data from a table based on selections from multiple checkboxes on the...
1
by: questionit | last post by:
If i open a form with filter like this: Call DoCmd.OpenForm("frm_myForm"", acNormal, " = " & Me.RefID) Now there is a subform in frm_myForm. How would i change the RecordSource of that...
4
by: questionit | last post by:
Hi I have a subform on a form, which has its recordset set. Textboxes on this subform has thier control source set as well. I was wondering if it would be possible to change both the...
4
by: questionit | last post by:
Hi everyone I want to open a form as a Dialog and then i want to change the form controls' property e.g make a textbox disabled etc. I've tried various ways but dont seems to work. I was...
8
Megalog
by: Megalog | last post by:
Hey guys.. my turn to ask a question: I'm having a weird issue with a form I've reworked. This form has a combo box, which when used is changing the recordsource of a subform. This subform has...
6
by: svdoerga | last post by:
I have a weird problem (or I'm overseeing something plain). I have a form which I open in formview with a query as recordsource (set in the design view). On opening, depending on some condition, the...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.