List Boxes ... again (1 Viewer)

estelleh

Member
Local time
Today, 20:16
Joined
Jul 16, 2021
Messages
56
Good morning!

I have a Jobs form which calls a Job Search form with a button. The job form is populated from the jobs table, with Customer name from the Customer table and Factory name from the Factory table. The Recordsource for the Jobs form is set from a query I built:
Code:
SELECT Jobs.*, Customer.CustName, Factory.FactoryName FROM Factory INNER JOIN (Customer INNER JOIN Jobs ON Customer.CustCode = Jobs.CustCode) ON Factory.FactoryCode = Jobs.FactoryCode;

The Job Search form has 2 listboxes. The first one is to search for the customer and has a textbox (txtSearch) that activates a "search as you type" query. The Row Source for the Customer listbox is a query:

lstCustName Query.JPG

and the On Change event of the text box contains the following code:
Code:
'Check if something has been typed in the search box. If not get no. of characters in search box for search query
If blnSpace = False Then
 Me.Requery
 Refresh
 txtSearch.SetFocus
 txtSearch.SelStart = Len(Me.txtSearch.Text)
End If
This works perfectly. I then have a second list box which displays all the jobs for the customer selected in listbox 1. The box populates properly, but the textbox doesn't activate a "search as you type" action. As far as I can see, I have all the same code as for the first list box and have changed the name of the search textbox to txtSearchJob and the listbox name to lstJobName.
lstJobName Query.JPG


The On Change event of txtSearchJobs contains the code
Code:
If blnSpace = False Then
 Me.Requery
 Refresh
 txtSearchJob.SetFocus
 txtSearchJob.SelStart = Len(Me.txtSearchJob.Text)
End If
Nothing changes in the Job Name list box when I type in the txtSearchJob textbox and I cannot see why!

Second problem is once I selectthe required job, I'm not sure how to change the recordsource for frmJobs to load the selected job. I have tried every which way I can think of with varying results - I managed to get the correct job loaded, but got a #NAME? for the Customer Name. Now I can't get the correct job loaded ... how do I get the Jobs form to load the Job selected in the list box, as well as getting the Customer Name from the Customer Table and the Factory Name from the Factory table?

I am currently setting the recordsource of the Jobs form on selecting a job to this (I don't even know how I got here any more 🙈)
Forms!frmJobs.RecordSource = "SELECT Jobs.*, Customer.CustName, Factory.FactoryName FROM Factory INNER JOIN (Customer INNER JOIN Jobs ON Customer.CustCode = Jobs.CustCode) ON Factory.FactoryCode = Jobs.FactoryCode WHERE (Customer.[CustCode])= Me.lstCustName" but it asks for a parameter value for lstCustName (which is the cust code) - I have checked (via textboxes on the form) that the ListIndex for both listboxes are correct.

Hope this makes sense to someone.....
 

isladogs

MVP / VIP
Local time
Today, 18:16
Joined
Jan 14, 2017
Messages
18,186
Its too early to take all that in. Need more coffee first!

You are using the same variable blnSpace in both sets of code. How is the value of blnSpace set?
You shouldn't need to refresh as well as requery.

The #name# error occurs when Access cannot find the field in the record source. That's likely to be why you're getting a parameter dialog as well
 

estelleh

Member
Local time
Today, 20:16
Joined
Jul 16, 2021
Messages
56
Its too early to take all that in. Need more coffee first!

You are using the same variable blnSpace in both sets of code. How is the value of blnSpace set?
You shouldn't need to refresh as well as requery.

blnSpace is set in the Key-Press Event of both listboxes to check if the sp[acebar has been ptressed
Code:
If KeyAscii = 32 Then
  blnSpace = True
Else
  blnSpace = False
End If
The #name# error occurs when Access cannot find the field in the record source. That's likely to be why you're getting a parameter dialog as well
That's where I need help with the 2nd part of my question.... How on earth do I get the Jobs form to load the correct record, with the 2 associated fields (The relationships are there). Recordsource? If so, what the heck am I supposed to set it to? I have tried to set a textbox each on the form to the cust code and job no (not ideal, I know) but I still get the parameter query, just for the textbox not the listbox.

I think I've just reached the Peter Principle of Access :p:eek:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:16
Joined
May 7, 2009
Messages
19,169
only noticed, you only need to Requery the listbox and not the whole form.
 

estelleh

Member
Local time
Today, 20:16
Joined
Jul 16, 2021
Messages
56
only noticed, you only need to Requery the listbox and not the whole form.
I don't understand? The list boxes are on the search form. The job form needs to be requeried / loaded with the job selected on the search form?
 

Users who are viewing this thread

Top Bottom