VBA code for combobox

[Edward]

Hi everybody,
I have a form that has a combobox with three columns, staff ID( hidden ),
last name, first name . and there are three checkboxes on the form attend 1.
attend 2, attend 3.
I want to write a vba code it combobox change event that when we select a
staff , it will check or uncheck those three checkboxes based in data in each
staff's record.
I have so far

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM [ession-staff-schedule] WHERE
Staff_ID='" & [cboFirstNameRes].Column(0) & "';")

chkIntro.Value=rst![Attend 1]
but i get mismatch error on the Set rst line any thoughts?
Thanks

 

[Albert D. Kallal]

Perhaps you try a continues form, and let the user actually "click" on the
check box column?
This would not require any coding on your part at all.

I have some screen shots here..and you can see where I used a continues form
for the check box...

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

I not really sure that just "selecting" a name in a combo box makes sense to
"check" box a column. What happens if the user selects the wrong name? (.how
are you now going to "un-check" the selection?) What about selecting
several names, and then changing ones mind? It seems the user will get
confused very quickly here, and worse the code is likely to get real messy
in a real hurry as you try to come up with a way to un-check the column...

I am just thinking aloud here, but I would consider something like a list
box, or a continues form with a check box column...

 

[Edward]

Thanks, what im trying to do not to check any checkboxes upon selection a
name from combo. I just want to show the user that what are the status of a
staff in three checkboxes based on any name he selects.
name Attend one attend 2 attend 3
X yes no no

so i have this info for a given staff , in a separate form when we select a
name ( e.g. X) I want to show those attend status in a table or a checkbox.

like
x

attend 1 yes
attend 2 no
attend 3 no

 

[Albert D. Kallal]

Ah, good, I did miss you goal.

So, the checkboxs are alreday "set" somewhere else.

Ok, just include the additonal collums in the combo box.

Then, for the 3 text boxes, just set the "source" of those boxes as:

=(combboxName.Column(1))

Note that the collums "start" at 0.....

 

[Edward]

Thanks, I guess that's a possibility but I don't know how to write that
select statement for combobox , because right now I have a Row source for
combobox as follow
Select Session.SessionID, Session.Sessaion.date & "," & Session.SessinTime
FROM session
and combo is formatted as
column 2
column width 0,4 to hide the ID field

Those check box data that you suggested I can add to the combo are from
another table ( not Session table) so how can I modify the above code to
populate a new column in combo with data from another table.

I still prefer to know how can I write the code in combos change event
because it will give me more flexability

I have done up to this point
private sub cboFirstNameRes_ change ()
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM [Session-staff-schedule] WHERE
Staff_ID='" & [cboFirstNameRes].Column(0) & "';")

chkIntro.Value=rst![Attend 1]

 

[Albert D. Kallal]

Thanks, I guess that's a possibility but I don't know how to write that
select statement for combobox , because right now I have a Row source for
combobox as follow
Select Session.SessionID, Session.Sessaion.date & "," & Session.SessinTime
FROM session

( not Session table) so how can I modify the above code to
populate a new column in combo with data from another table.

Well, don't need the above now that I grasping your problem....

private sub cboFirstNameRes_ change ()

Don't use the chnage event, it will fire for EACH charater you type. You
want to use the after update event of the combo box that ONLY fires after
the user has actually selected a value from the combo list.

dim strSql as string
dim rst as dao.RecordSet

strSql = "SELECT * FROM [ession-staff-schedule] WHERE " & _
"Staff_ID = '" & me.cboFirstNameRes & "'"

keep in mind if staff_id is a "number" field and not a string (text), then
the above does NOT need the quotes eg:

strSql = "SELECT * FROM [ession-staff-schedule] WHERE " & _
"Staff_ID = " & me.cboFirstNameRes

Set rst = currentDB.OpenRecordset(strSql)

chkIntro.Value=rst("Attend 1")

rst.Close

We also did not need the .column(0) since the combo box likey returns the 1
collum anyway...

Also, try to avoid spaces in your field names...they will cause you great
suffering and pain over time....