assigning default value to combo box

[Todd]

I have a form which contains a combo box that allows my
users to enter which Shift they are on, which is saved
with each status record that they enter.

I have a table that looks like this.

Shift User
A Bill
B Ted
C Mark
A Betty
A Jen
C Fred

Right now the way the combo box is setup, the rowsource
contains a query which returns the different shift letters
as choices in the combo box.

What I want to do is make it so that the default value for
the combo box is the users normal shift (ie per the
table). Each users database username is the same as the
value in this table so I'm hoping I can use the CurrentUser
()function, compare it to the values in this table, and
return the default value as the correct shift per the
table. I want to keep the combo box format, because
occasionally people work different shifts, so I still want
them to have the option to change the shift entry.

Can anyone help with this? Although I know how to write
the query to do this, I can't seem to make a query work
for the default value.

The query that works is:

SELECT [ShiftList].[Shift] FROM [ShiftList] WHERE
(((CurrentUser())=[ShiftList].[User]));

 

[Trias]

Hi Todd

try to add this lines of code in the Form_Open Event

Dim sShift As Strin
Const DQ = """

'get the Shift ID for the current use

sShift = Nz(DLookup("Shift", "ShiftList", "User='" & CurrentUser & "'"), ""
'check to see if you got valid shift i
If Len(sShift) > 0 The
Me.Combo1.DefaultValue = DQ & sShift & D
End I


----- Todd wrote: ----

I have a form which contains a combo box that allows my
users to enter which Shift they are on, which is saved
with each status record that they enter

I have a table that looks like this

Shift Use
A Bil
B Te
C Mar
A Bett
A Je
C Fre

Right now the way the combo box is setup, the rowsource
contains a query which returns the different shift letters
as choices in the combo box

What I want to do is make it so that the default value for
the combo box is the users normal shift (ie per the
table). Each users database username is the same as the
value in this table so I'm hoping I can use the CurrentUse
()function, compare it to the values in this table, and
return the default value as the correct shift per the
table. I want to keep the combo box format, because
occasionally people work different shifts, so I still want
them to have the option to change the shift entry

Can anyone help with this? Although I know how to write
the query to do this, I can't seem to make a query work
for the default value

The query that works is

SELECT [ShiftList].[Shift] FROM [ShiftList] WHERE
(((CurrentUser())=[ShiftList].[User]));

 

[Todd]

Thanks. Code works great.

I actually put it into the Current Event, and it seems to
work well there. This way as users add records it runs
every time they move to a new record.

I think on the Open Event, it only updates the first
record when the form is opened.

Do you see anything wrong with this?

 

[Trias]

Hi Todd,

glad that it helps. If you put the code in the Form_Open event is more efficient than in Current event. It will only get excute 1 time instead couple of times (if user creates many records). Moreover, i test the code before i send you and it works in my test db.

HTH

----- Todd wrote: -----

Thanks. Code works great.

I actually put it into the Current Event, and it seems to
work well there. This way as users add records it runs
every time they move to a new record.

I think on the Open Event, it only updates the first
record when the form is opened.

Do you see anything wrong with this?

-----Original Message-----
Hi Todd,

try to add this lines of code in the Form_Open Event.
Dim sShift As String Const DQ = """"
'get the Shift ID for the current user
sShift = Nz(DLookup("Shift", "ShiftList", "User='" &

CurrentUser & "'"), "")
'check to see if you got valid shift id
If Len(sShift) > 0 Then
Me.Combo1.DefaultValue = DQ & sShift & DQ
End If

I have a form which contains a combo box that allows

my
users to enter which Shift they are on, which is saved
with each status record that they enter.

I have a table that looks like this.
Shift User

A Bill
B Ted
C Mark
A Betty
A Jen
C Fred

Right now the way the combo box is setup, the

rowsource
contains a query which returns the different shift letters
as choices in the combo box.

What I want to do is make it so that the default

value for
the combo box is the users normal shift (ie per the
table). Each users database username is the same as the
value in this table so I'm hoping I can use the CurrentUser
()function, compare it to the values in this table, and
return the default value as the correct shift per the
table. I want to keep the combo box format, because
occasionally people work different shifts, so I still want
them to have the option to change the shift entry.

Can anyone help with this? Although I know how to

write
the query to do this, I can't seem to make a query work
for the default value.

The query that works is:
SELECT [ShiftList].[Shift] FROM [ShiftList] WHERE (((CurrentUser())=[ShiftList].[User]));
.