MS Access vba code to auto select a pre-existing value from a combo box based on criteria

I have a subroutine that is supposed to auto select a value from a drop down combo box based on the IF statement criteria.  When I run it I get an error "the value you entered isn't valid for this field")

the code faults at this line:
                       Forms!CRF.comSystemLevel.Value = "WCD 4000"





Public Sub systemlevelx()

Dim strlsn As String
Dim strsystem As String
Dim rstlsn As DAO.Recordset
Dim rstsystem As DAO.Recordset




strsystem = "SELECT [Description] FROM dbo_CodeSystemLevel WHERE Active = 1"
Set rstsystem = CurrentDb.OpenRecordset(strsystem, dbOpenDynaset, dbSeeChanges)


strlsn = "SELECT [LSN] FROM dbo_CrfPartDetails WHERE RefNumber = " & Forms!CRF.ComplaintRefNumber & ""
Set rstlsn = CurrentDb.OpenRecordset(strlsn, dbOpenDynaset, dbSeeChanges)
If rstlsn.RecordCount > 0 Then
    rstlsn.MoveLast
    rstlsn.MoveFirst
End If

lotserial = rstlsn![LSN]

IIf lotserial Like "07*" Or lotserial Like "59*" Or lotserial Like "58*" Or lotserial Like "86*" Then
    Forms!CRF.comSystemLevel.SetFocus
    Forms!CRF.comSystemLevel.Dropdown
    Forms!CRF.comSystemLevel.Value = "WCD 4000"
End If

End Sub
sxxguptaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
What is the rowsource property of your combo box?

Just guessing that comSystemLevel stores a numeric ID rather than text.

If that is the case, you would have to lookup the ID corresponding to "WCD 4000", with code similar to this:


Forms!CRF.comSystemLevel.Value =  DLookup("YourIDField", "YourTable", "YourTextField = 'WCD 4000'")

Open in new window


You'd have to use your own actual table and field names in my sample code.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rey Obrero (Capricorn1)Commented:
what is the Row Source of combo "comSystemLevel" ?
0
sxxguptaAuthor Commented:
Hi:

Row Source:
SELECT dbo_CodeSystemLevel.SystemCode, dbo_CodeSystemLevel.Description, dbo_CodeSystemLevel.Active FROM dbo_CodeSystemLevel WHERE (((dbo_CodeSystemLevel.Active)=1));

Bound Column:1
0
sxxguptaAuthor Commented:
Thank you.
0
mbizupCommented:
Glad to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.