ListBox VBA (1 Viewer)

PatAccess

Registered User.
Local time
Today, 14:27
Joined
May 24, 2017
Messages
284
Hello Guys,
I need some help.
I have a form with a textbox (to type a name), a listbox1 (that brings up the licenses for each person), and another listbox2 (that is supposed to bring up certificates associated with that license).
From the name textbox to the 1st listbox1 it works. Now when I want to click one of the value in that listbox1 to generate the certificate associated with it in listbox2 nothing happens.


Here is the code from textbox to listbox 1

Private Sub cmdSearchName_Click()
Dim strLoad As String
Dim task As String
Dim strSpacefix As String

If Not IsNull(Me.FName) Then
'Load the text box contents
strLoad = Me.FName.Value
strSpacefix = Replace(strLoad, "", "" & "*"" AND [EmpID] LIKE ""*" & "", 1, -1)

task = "SELECT State,LicNum,Expires FROM Qry_PEStateLicCert WHERE ([FName] LIKE ""*" & strSpacefix & "*"")"

Me.ListBoxStateLic.RowSource = task
Me.ListBoxStateLic.Requery

Me.ListBoxStateLic = Me.ListBoxStateLic.ItemData(1)
End If

End Sub

Private Sub FName_AfterUpdate()
Call cmdSearchName_Click
End Sub

but I am stuck at to what to do for ListBox2 (ListCert)

Thank you for your help
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:27
Joined
Aug 30, 2003
Messages
36,118
Presuming the first listbox is single select, in its after update event you'd set the row source of the second listbox using the value from the first. I'm confused as to what

strSpacefix = Replace(strLoad, "", "" & "*"" AND [EmpID] LIKE ""*" & "", 1, -1)

is supposed to accomplish.
 

PatAccess

Registered User.
Local time
Today, 14:27
Joined
May 24, 2017
Messages
284
Hello, I have placed that code on there for the text box so that they can type a name.
But isn't it what this code is doing?

Private Sub ListBoxStateLic_AfterUpdate() 'ListBox1
Dim task As String

task = "SELECT Cert,LicNum FROM Qry_PEStateLicCert WHERE (LicNum=" & Me.LicNum & " AND Cert= " & Me.Cert & ")" 'Show LicNum in ListBox2
Me.ListCert.RowSource = task 'ListBox2
Me.ListCert.Requery

'Me.ListCert = Me.ListCert.ItemData(1)
End Sub

ListBox1 and ListBox2 have LicNum in common
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:27
Joined
Aug 30, 2003
Messages
36,118
That doesn't appear to refer to the listbox, it refers to what I assume is a textbox: Me.LicNum
 

Dreamweaver

Well-known member
Local time
Today, 18:27
Joined
Nov 28, 2005
Messages
2,466

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:27
Joined
Aug 30, 2003
Messages
36,118
Far as I can tell it doesn't do anything:

?Replace("Paul", "", "" & "*"" AND [EmpID] LIKE ""*" & "", 1, -1)
Paul
 

PatAccess

Registered User.
Local time
Today, 14:27
Joined
May 24, 2017
Messages
284
Hi Guys,
That code might be an extra but my problem is the connection between listbox1 and listbox2. when I click on an item in listbox1 nothing shows in listbox2. what am I doing wrong there? I have tried everything with this code:
Private Sub ListBoxStateLic_AfterUpdate() 'ListBox1
Dim task As String

task = "SELECT Cert,LicNum FROM Qry_PEStateLicCert WHERE (LicNum=" & Me.LicNum & " AND Cert= " & Me.Cert & ")" 'Show LicNum in ListBox2
Me.ListCert.RowSource = task 'ListBox2
Me.ListCert.Requery

'Me.ListCert = Me.ListCert.ItemData(1)
End Sub
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 05:27
Joined
Oct 20, 2008
Messages
512
Hi Guys,
That code might be an extra but my problem is the connection between listbox1 and listbox2. when I click on an item in listbox1 nothing shows in listbox2. what am I doing wrong there? I have tried everything with this code:
Private Sub ListBoxStateLic_AfterUpdate() 'ListBox1
Dim task As String

task = "SELECT Cert,LicNum FROM Qry_PEStateLicCert WHERE (LicNum=" & Me.LicNum & " AND Cert= " & Me.Cert & ")" 'Show LicNum in ListBox2
Me.ListCert.RowSource = task 'ListBox2
Me.ListCert.Requery

'Me.ListCert = Me.ListCert.ItemData(1)
End Sub

After assigning the Sql to task, next line do a debug.print task and show us the result. I've highlighted in red possible problem areas. Is that a period between Cert.LicNum? In the Where clause, are they numbers, if not, and they're string values, they should be quoted.

Edit: The Where Clause criteria, are those values from columns of ListBox1?
 

PatAccess

Registered User.
Local time
Today, 14:27
Joined
May 24, 2017
Messages
284
Hello Guys,
I stepped away from this for a little but I am back on it and it is giving me issues.

Here is the code:
Private Sub cmdCert_Click()
Dim strtask As String
strtask = "SELECT Qry_PEStateLicCert.Cert" & _
"FROM Qry_PEStateLicCert" & _
"WHERE Qry_PEStateLicCert.Cert= '" & Me.Cert & "'"
Debug.Print strtask
Me.ListCert.RowSource = strtask 'ListBox2
Me.ListCert.Requery

'Me.ListCert = Me.ListCert.ItemData(1)


End Sub

Now it is outputting the actual SQL Language within the listBox. I only need the cert field to show based on an item selected from the previous listBox

My immediate window shows me this:
SELECT Qry_PEStateLicCert.CertFROM Qry_PEStateLicCertWHERE Qry_PEStateLicCert.Cert= ''

Now I placed this code in the click_event_property of a cmd button and then I call it on the After-Update_Event of the previous box. What am I missing now?

Thank you Guys
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:27
Joined
Aug 30, 2003
Messages
36,118
The debug shows that you're not getting a value from Me.Cert. Is there supposed to be something there?

What is the Row Source Type property? It should be Table/Query.
 

PatAccess

Registered User.
Local time
Today, 14:27
Joined
May 24, 2017
Messages
284
Yes there is filepath to that certificate and the Row Source Type was set to value list. when I set it to Table/Query it is blank at runtime.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:27
Joined
Aug 30, 2003
Messages
36,118
Double check the spelling of the control, its contents, etc. The debug is clearly showing that there is no value in Me.Cert. If it's a combo, make sure you have the right column. You can set a breakpoint and hover over Me.Cert to see what it contains, or use this in the Immediate window:

?Me.Cert
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 05:27
Joined
Oct 20, 2008
Messages
512
Hello Guys,
I stepped away from this for a little but I am back on it and it is giving me issues.

Here is the code:
Private Sub cmdCert_Click()
Dim strtask As String
strtask = "SELECT Qry_PEStateLicCert.Cert" & _
"FROM Qry_PEStateLicCert" & _
"WHERE Qry_PEStateLicCert.Cert= '" & Me.Cert & "'"
Debug.Print strtask

Me.ListCert.RowSource = strtask 'ListBox2
Me.ListCert.Requery

'Me.ListCert = Me.ListCert.ItemData(1)


End Sub

Now it is outputting the actual SQL Language within the listBox. I only need the cert field to show based on an item selected from the previous listBox

My immediate window shows me this:
SELECT Qry_PEStateLicCert.CertFROM Qry_PEStateLicCertWHERE Qry_PEStateLicCert.Cert= ''

Now I placed this code in the click_event_property of a cmd button and then I call it on the After-Update_Event of the previous box. What am I missing now?

Thank you Guys
I can't see how in your immediate window you got the result you posted, what I highlighted in red above. (I don't normally use the end of line _ character to continue string building on the following lines, but I assume that is set up correctly.)


Did you actually directly copy the result of the debug.print statement into your post?


EDIT/UPDATE: Old eyes at play at my end. Space out your From and Where, that is, enter a space before the words From and Where, for starters.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:27
Joined
Aug 30, 2003
Messages
36,118
What would you expect Steve? It looks appropriate to me, though looking at it again made me notice the lack of spaces between lines (before FROM and WHERE).
 

essaytee

Need a good one-liner.
Local time
Tomorrow, 05:27
Joined
Oct 20, 2008
Messages
512
What would you expect Steve? It looks appropriate to me, though looking at it again made me notice the lack of spaces between lines (before FROM and WHERE).
Yep, I picked up on that just after posting. Damn, forgot to read twice, submit once.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 05:27
Joined
Jan 20, 2009
Messages
12,849
I don't normally use the end of line _ character to continue string building on the following lines, but I assume that is set up correctly.

No they are incorrect too. They have omitted the space.

Line continuation is <space><underscore>
 

Cronk

Registered User.
Local time
Tomorrow, 05:27
Joined
Jul 4, 2013
Messages
2,770
And even with spaces between the query clauses, unless there's any records with a ZLS in the query, there will be nothing in the list box, given that Me.Cert =''
 

Cronk

Registered User.
Local time
Tomorrow, 05:27
Joined
Jul 4, 2013
Messages
2,770
And if
Now it is outputting the actual SQL Language within the listBox

it is likely that the listbox RowSourceType is set to Value List, not Table/query
 

Users who are viewing this thread

Top Bottom