Listbox help needed, please (1 Viewer)

estelleh

Member
Local time
Today, 23:45
Joined
Jul 16, 2021
Messages
56
I have a search form which contains 2 listboxes. The first one lists all the customers and when the user picks a customer (On Click event) the second listbox displays all the jobs for that customer. lstCustName is populated from the Customer table with CustNo and CustName, bound on CustCode. lstJobName will be populated with JobNo and JobName when a customer is selected in ListBox1 as follows:

Code:
lstJobName.RowSource = "SELECT Jobs.JobNo, Jobs.JobName FROM Jobs WHERE [Jobs].[CustCode]= CustCode ORDER BY Jobs.JobNo DESC"

I also have a textbox on the form which is bound to CustCode (I thought my problems may stem from trying to pick up the selected custCode straight from lstCustName.

My problems are many and varied, so apologies in advance for the length.

1) When I drop lstJobName onto the form, I select these options:
a) get values from a query containing the code above​
b) Select JobNo and JobName as fields to display in listbox​
c) Select JobNo as the unique identifier​
When I select "Store that value in this field", the drop down list contains CustCode and CustName as options, not JobNo and JobName as I would expect? Why?

So for now I am selecting "Remember the value for later use". The JobNo value must be passed back to the original form.

2) When I load the search form, the Customer listbox has the 1st record in the table selected as a default, although there is no value in the "Default Value" . Is it possible to have no selection in the listbox after populating it? I thought it may be a tab stop issue, so have made that No for the listboxes, but it makes no difference.

3) How do I check if no records are found for the Jobs list box (i.e. no jobs for a specific customer)?

TIA :)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:45
Joined
May 7, 2009
Messages
19,169
2. add code to the load event of your form, to set the listbox value to non existing in the list:

private sub form_load()
me.listbox1 = -1
me.listbox2 = -1
end sub

3. you use DCount() function:

If DCount("X", "Jobs", "[CustCode]= " & [CustCode]) <> 0 Then
'there is a job for this customer
Else
'no job
End If
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:45
Joined
Oct 29, 2018
Messages
21,358
1. To bind the listbox to jobno, jobno has to be included in the record source of your form.
 

estelleh

Member
Local time
Today, 23:45
Joined
Jul 16, 2021
Messages
56
@arnelgp Thanks. Have tried # 2 - without success, sadly. It adds -1 as the first entry in the listbox and selects that 🙈.

lstCustName.JPG


I will have a look at your solution to #3 tomorrow - my brain hurts now - time for a G&T! Have a great weekend, both of you :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:45
Joined
Feb 19, 2002
Messages
42,976
The list you are displaying is incorrect. It should NOT contain duplicates. Use a query that groups the data to eliminate the duplicates or show additional columns so the user can select the correct item.

lstJobName.RowSource = "SELECT Jobs.JobNo, Jobs.JobName FROM Jobs WHERE [Jobs].[CustCode]= CustCode ORDER BY Jobs.JobNo DESC"
This isn't filtering the second listbox correctly. Change it to:

lstJobName.RowSource = "SELECT Jobs.JobNo, Jobs.JobName FROM Jobs WHERE [Jobs].[CustCode]= '" & Me.CustCode & "' ORDER BY Jobs.JobNo DESC"

I assumed CustCode was a string. If it is numeric, remove the single quotes.
 

estelleh

Member
Local time
Today, 23:45
Joined
Jul 16, 2021
Messages
56
The list you are displaying is incorrect. It should NOT contain duplicates. Use a query that groups the data to eliminate the duplicates or show additional columns so the user can select the correct item.


This isn't filtering the second listbox correctly. Change it to:

lstJobName.RowSource = "SELECT Jobs.JobNo, Jobs.JobName FROM Jobs WHERE [Jobs].[CustCode]= '" & Me.CustCode & "' ORDER BY Jobs.JobNo DESC"

I assumed CustCode was a string. If it is numeric, remove the single quotes.
Thanks Pat.

I tried changing the rowsource to your code, but I get an error either way - with or without the single quotes I get a "Compile error: Syntax error"
Code:
lstJobName.RowSource = "SELECT Jobs.JobNo, Jobs.JobName FROM Jobs WHERE [Jobs].[CustCode]='"&Me.CustCode&"' ORDER BY Jobs.JobNo DESC"

I have changed it back to the way I had it and it seems to work? 🤷‍♀️
Capture.JPG


The duplicates in list 1 is just bad data capturing for testing :) - the duplicate names do have different customer codes.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:45
Joined
Feb 19, 2002
Messages
42,976
"Compile error: Syntax error"
Although the message doesn't point to the error, perhaps if you compared what you typed to what I typed, you would see it or them.

The &'s need a space before and after.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:45
Joined
May 7, 2009
Messages
19,169
i've made "somewhat' similar?
click on the listbox and type something.
 

Attachments

  • listbox_search2.accdb
    5.6 MB · Views: 303

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:45
Joined
May 21, 2018
Messages
8,463
@tfaisal
You totally hijacked an ongoing thread. Strongly recommend you delete your post in this thread and only use your new thread. You took an ongoing topic and changed the subject.
 

Users who are viewing this thread

Top Bottom