Link to home
Create AccountLog in
Avatar of wlwebb
wlwebbFlag for United States of America

asked on

Access VB combobox Where clause based on a text String

Hello All,
Trying to get a Form's Combobox to update based on a Text String Where Clause.

I am uploading a stripped down of the db.  The form where I am having all the trouble is
sfrm_Employee_NameViaUser which is the only form in this stripped down db.

At the bottom of that form you will see One textbox and One combobox that are in Bold Font.  These are where I'm having an Issue.  

The Textbox has an After_Update event to cause the Combobox to update (Because I don't know any other way to force it to update if the User is simply Page Up or Page Down through records if nothing changes then can't get that combobox to update when just viewing each record on form, but that's a different issue)

My code on that text box is:
Private Sub txtTest1_AfterUpdate()
Dim strTestPRID As String
strTestPRID = Me.txtTest

'Me.cboTest.RowSource = "SELECT EmpID, PRSysEmpID, BasePRID, NbrID, NbrIDVal FROM qry_dta_Employee_PRSysIDTestDuplicate WHERE BasePRID= " & strTestPRID
Me.cboTest.RowSource = "SELECT EmpID, PRSysEmpID, BasePRID, NbrID, NbrIDVal FROM qry_dta_Employee_PRSysIDTestDuplicate WHERE BasePRID= '" & strTestPRID & "'"
Me.cboTest = Me.cboTest.ItemData(0)

End Sub

Open in new window


However, Even when I have the record of an existing employee up, nothing gets selected in the Combobox...
Employee.accdb
Avatar of Wim_Bl
Wim_Bl
Flag of Belgium image

Hi,

there is a slight error in your code, you are referencing the wrong textbox. If you change

Private Sub txtTest1_AfterUpdate()
Dim strTestPRID As String
strTestPRID = Me.txtTest

to

Private Sub txtTest1_AfterUpdate()
Dim strTestPRID As String
strTestPRID = Me.txtTest1.Value

everything works just fine.


Greetings
Avatar of wlwebb

ASKER

I think you have missed the String that it needs to compare to up in Me.txtTest  I have a bold caption to the right of it that says:
"This is the textstring that the combobox Where Clause needs to compare against"

my textbox txttest1 by changing this meaningless textbox is only a way to get the combobox to update.

The textbox TxtTest (not TxtTest1) is where the String resides that I need the combobox's Where clause to compare with.
Hi,

The value in txtTest is, for example, "AAAAAAZ". There is no record with this value returned from the query qry_dta_Employee_PRSysIDTestDuplicate so the combo box is empty.

I can't understand why you have txtTest1 at all, is it just to try to work out this problem? If you want to change the rowsource for the combo box then do it when the value in txtTest changes, or when the record is made current, for example.
SOLUTION
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of wlwebb

ASKER

Irog
on that form, close it and then reopen it after you have made the move of that code to the OnCurrent Event.......  What do you get??????????


PS......  All of those calculated fields at the top right (the right most 3 columns and 3 rows) actually is where I broke down the 1 textbox with the Label field caption "Test Base PRSysID No Nbr" towards the bottom right of the form.  When I started getting an error and couldn't get the combobox to work I thought it might be one of the segments of that textboxes code.
I get a blank data entry form with AaaaAaZ as the Suggested PRSysID.  What are you getting?
Avatar of wlwebb

ASKER

I get errors..... and the code kicks to an break mode
Avatar of wlwebb

ASKER

It breaks on the Form_Current
strTestPRID = Me.txtTest
gives me Error 13
Type mismatch

???????????
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of wlwebb

ASKER

Thanks All...
Appreciate the help