Repost about auto populating a field

[JohnB]

Hi.

I posted yesterday about getting some fields to auto populate when a combo
is updated. I've had no replies yet but have now found a similar post which
provided code that I have changed to the following, which works as far as it
goes but I want to modify it.

Private Sub cboSchoolName_AfterUpdate()

On Error GoTo Err_cboSchoolName_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "SchoolID = " & Me!cboSchoolName

' Look up MentorID and assign it to cboMentor.
Me!cboMentor = nz(DLookup("MentorID", "qrySECPlacementsAll", strFilter),0)


Exit_cboSchoolName_AfterUpdate:
Exit Sub

Err_cboSchoolName_AfterUpdate:
MsgBox Err.Description
Resume Exit_cboSchoolName_AfterUpdate

End Sub

The thing is , I want to use a more complicated condition and I've tried
changing the strFilter to the following. I think you'll see what I'm trying
to do:
strFilter = ("SchoolID = " & Me!cboSchoolName) And ("PlacementStage = " &
Me!cboPlacementStage)

I get a type mismatch error when I use this. Am I doing this correctly - is
it a syntax problem? Or is there a better way to do this? I actually want to
have about four "And" conditions built into the DLookup.

Thanks for any help, JohnB

 

[Nikos Yannacopoulos]

John,

Your filter will work, if changed to:

strFilter = "SchoolID = " & Me!cboSchoolName & " And " & _
"PlacementStage = " & Me!cboPlacementStage

provided (a) SchoolID and PlacementStage are both numeric, and (b)
cboSchoolName and cboPlacementStage return numeric values (regardless of
what they display!). Notice the And operand is enclosed in the quotes,
as it is part of the text string. Only the references to the combos must
be left outside the quotes, so they are treated as such, inserting the
corresponding values in the string.

If, on the other hand, ScoolID , for instance, is actually text, and
cboSchoolName returns the correct text string, then the syntax should be:

strFilter = "SchoolID = '" & Me!cboSchoolName & "' And " & _
"PlacementStage = " & Me!cboPlacementStage

Notice the single quotes aroung the combo reference (and iside the
double ones) which denotes the value returned from the combo as a
string. If they are both text:

strFilter = "SchoolID = '" & Me!cboSchoolName & "' And '" & _
"PlacementStage = " & Me!cboPlacementStage & "'"

HTH,
Nikos

 

[Ofer]

You can try this

Assuming that the fields are numbers
strFilter = "SchoolID = " & Me!cboSchoolName & " And PlacementStage = " &
Me!cboPlacementStage

If the fields are string add a single quote before and after
strFilter = "SchoolID = '" & Me!cboSchoolName & "' And PlacementStage = '"
&
Me!cboPlacementStage & "'"

 

[JohnB]

Thanks for such a good and quick reply. I've used your suggestion and it
works a treat - the fields are actually a mixture of number and string, so my
code is now:

strFilter = "SchoolID = " & Me!cboSchoolName & " And PlacementStage = '" &
Me!cboPlacementStage & "'" & " And Subject = '" & Me!txtSubject & "'"

One further question - if I wanted to add another "And" condition and this
time specify myself what should be in the field, how would I do that. e.g. if
I wanted the record selected to also have the word 'Live' in field 'Status'.

Thanks again. JohnB

 

[JohnB]

Thank you Nikos.

In fact I had seen the reply from Ofer first so I used his variation but I'm
sure yours would have been just as easy for me to understand. I'll keep a
note of your suggestion for future use too. Thanks also for such a good
explanation of the various options - just what I need.

Cheers, JohnB

 

[Ofer]

If I understood you correctly, then

strFilter = "SchoolID = " & Me!cboSchoolName & " And PlacementStage = '" &
Me!cboPlacementStage & "'" & " And Subject = '" & Me!txtSubject & "' And
Status = 'Live'"

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[JohnB]

Hmm. Something not right here. I get a type mismatch error. My code is now:

strFilter = "SchoolID = " & Me!cboSchoolName & " And PlacementStage =
'" & Me!cboPlacementStage & "'" & " And Subject = '" & Me!txtSubject & "'"
And Status = 'Live'"

Have I got the syntax right?. Ive tried And Status = "'Live'" but that
doesn't work either


Please bear with me - I'm not able to get to the database again until Monday
but if you respond, I will reply on this thread then. Many thanks for the
help. JohnB

 

[Ofer]

It should be

strFilter = "SchoolID = " & Me!cboSchoolName & " And PlacementStage =
'" & Me!cboPlacementStage & "'" & " And Subject = '" & Me!txtSubject & "'
And Status = 'Live'"

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

 

[JohnB]

Ah yes. I see the difference. I'll try this out on Monday and will reply
again then. Thanks again, JohnB

 

[JohnB]

Hi again.

Yes, that works OK. Also a " & " seems to have crept in there - probably
caused by word wrap when I copied your code in. I'm not sure what the &
symbol actually does in the code and presumably " & " is benign. Anyway, I've
taken it out.

Many thanks for the help. JohnB

 

[Nikos Yannacopoulos]

John,

The ambsersand (&) simply concatenates text strings. Note that it is not
the ambersand that is enclosed in the quotes (" & "); it is the txt
strings to the left and right of it that are denoted by the quotes, and
the ambersand just concatenates them into a single text string.

HTH,
Nikos

 

[JohnB]

Thanks for that Nikos. Lots of learning to do.

JohnB