Execute looping query

[Bunky]

I have a form that has a USER code (8 unique char) in a combo box driven off
the Row Source. The Row Source is this - SELECT Staff.User,
Staff.Specialist, Staff.Skill, Staff.Supervisor FROM Staff WHERE
((Staff.Supervisor) Not Like 'Termed*') Order by User;. I then have 3
Txtboxes that I populate based on the data brought back from the Select.
Everything works perfectly when I am entering a new database record.

When I go back to an existing record, I do not have the Specialist, Skill
and Supervisor stored in the record so how do I get that data repopulated so
the operator can see who they are working Without doing the pull down again.
I'm sure it is VB code but I am still learning that on the fly.

Thanks in advance for your help.

 

[Dorian]

You could requery the combo box in the current event.
Me!MyComboBox.Requery
You will then have to move the hidden columns from the combo box to your
other text boxes.
Why not just widen your combo box so it shows the 4 columns returned. I'm
assuming the last 3 columns are hidden.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Bunky]

Thank you Dorian! I think I understand the concept the the syntax / whatever
is not working. Here is what I put on Current

If Me.User = "" Or IsNull(Me.User) Then
' This is to clean out the textboxes for a new record'
Me.TxtBox1.Value = Spaces
Me.TxtBox2.Value = Spaces
Me.TxtBox3.Value = Spaces
Else
Me!User.Requery
Me!User.Column(1) = Me.TxtBox1.Value
Me!User.Column(2) = Me.TxtBox2.Value
Me!User.Column(3) = Me.TxtBox3.Value
End If

When I try to execute it I get a runtime error 451
Ideas?

 

[Bunky]

Dorian.

I turned the value statements around and it worked perfectly! THANK YOU!!!!

Now you asked why not just widen the user combo box o show all 4 columns
returned. I never really thought of that. We have this routine that was
written a couple of yrs ago that populates the other areas based on the user
id and we never have needed to change it. I will look into that.

Thank You again!

 

[Douglas J. Steele]

Note that you could simplify your If statement to

If Len(Me.User & vbNullString) = 0 Then

although if your intent is to do this for new records only, you might want
to use

If Me.NewRecord Then

 

[Bunky]

Dorian,

I turned the statements around and it works perfectly! THANK YOU!

Now, you asked why not open the user combo box up to show all four elements.
Good question. I guess it is because I never have done it that way. I'll
tyr it! Thanks again!

Kent

 

[Bunky]

Thank you, Doug. I can always use the hints and tips!