Combo box default value

Not a problem.

I do need to ask a couple of question so I can get this right.
1. Are you using Access Security?
2. What is the name of your form?
3. What is the name of your combo box?
4. What is the name of the field your combo box is bound to?
5. What is the Row Source property of your combo box?
6. What is your experience level with VBA?

If you can just put the answers below the question, I can work it out for you.

"Matt Dawson" wrote:

I am using 2003 so that is why i am so confused about it all.

We could try VBA and I am hoping that will work. However, you mgiht have to
be patient with me.

It is much appreciated!

"Klatuu" wrote:



"Matt Dawson" wrote:

Not sure why I can't make it work when the combo is bound but for some reason
it cannot.
I don't understand this, it worked for me in 2003, what version are you using?

I need to to be bound as this is the only way i can make the reports update
with the correct agent ID. This is needed

Not really, If you want to try this approach, I can show you a VBA solution
that will take care of that. It is done all the time.


It does not need to be a combo, but it does need to be bound to Agent ID in
order for the reports to work. Is it definitely CurrentUser() that I need to
enter?

CurrentUser() is correct. However, if you are not using Access WorkGroup
Security, every user will return "admin"


"Klatuu" wrote:

It appears you are not familiar with VBA if you don't understand the
immediate window.

As I said before, it worked for me in both a bound and an unbound combo.

Just a thought, If the user is going to select his own ID, then is it
necessary to have a combo? Would they be able to select another ID? If they
are always going to use their own ID, then a text box with CurrentUser() in
the control source should do just fine. If not, then you could make the
combo unbound, put the CurrentUser() in the default value, create a text box
(it could be invisible) with the control source of =MyCombo, and Make the
text box the bound control.

"Matt Dawson" wrote:

If the control is unbound, then the theory works

However, the reports then do not work as they do not recognise the unbound
field and do not list the sent quotes by agent but simply as a blanks!

How do you mean "making the call in the immediate window"??

Matt

"Klatuu" wrote:

I tested it here (2003) on both a bound and an unbound combo using it as the
default value. If it is a bound control, it can't be in the control source.
That is why you are not able to update. The control source is where the
control is bound to the field.
Have you tried making the call in the immediate window?

"Matt Dawson" wrote:

Makes no difference. Any other thoughts?

Cant seem to make it work. If i take the combo box out it works, but not
with it. However, i can't set it without the combo box or set the control
value as CurrentUser() as this then changes and has a large effect on the
tables and report data. This does not update with that as the control value

Matt

"Klatuu" wrote:

Remove the = sign

"Matt Dawson" wrote:

I currently have combo box in which agents select their own ID when they are
completing a quote.
THey also have their own log ins and I was wondering if it was possible to
set this combo box default value to the current user.
I have tried entering =CurrentUser() in the default value but this does not
work

Any ideas?
Thanks,
Matt

Not a problem.

I do need to ask a couple of question so I can get this right.
1. Are you using Access Security? Yes I am using it with user passwords and permissions
2. What is the name of your form? Acceptance
3. What is the name of your combo box? Agent ID
4. What is the name of the field your combo box is bound to? Agent ID
5. What is the Row Source property of your combo box? Agents
6. What is your experience level with VBA? Not very good, used it for very simple things such as select, from, where etc

If you can just put the answers below the question, I can work it out for you.

"Matt Dawson" wrote:

I am using 2003 so that is why i am so confused about it all.

We could try VBA and I am hoping that will work. However, you mgiht have to
be patient with me.

It is much appreciated!

"Klatuu" wrote:



"Matt Dawson" wrote:

Not sure why I can't make it work when the combo is bound but for some reason
it cannot.
I don't understand this, it worked for me in 2003, what version are you using?

I need to to be bound as this is the only way i can make the reports update
with the correct agent ID. This is needed

Not really, If you want to try this approach, I can show you a VBA solution
that will take care of that. It is done all the time.


It does not need to be a combo, but it does need to be bound to Agent ID in
order for the reports to work. Is it definitely CurrentUser() that I need to
enter?

CurrentUser() is correct. However, if you are not using Access WorkGroup
Security, every user will return "admin"


"Klatuu" wrote:

It appears you are not familiar with VBA if you don't understand the
immediate window.

As I said before, it worked for me in both a bound and an unbound combo.

Just a thought, If the user is going to select his own ID, then is it
necessary to have a combo? Would they be able to select another ID? If they
are always going to use their own ID, then a text box with CurrentUser() in
the control source should do just fine. If not, then you could make the
combo unbound, put the CurrentUser() in the default value, create a text box
(it could be invisible) with the control source of =MyCombo, and Make the
text box the bound control.

"Matt Dawson" wrote:

If the control is unbound, then the theory works

However, the reports then do not work as they do not recognise the unbound
field and do not list the sent quotes by agent but simply as a blanks!

How do you mean "making the call in the immediate window"??

Matt

"Klatuu" wrote:

I tested it here (2003) on both a bound and an unbound combo using it as the
default value. If it is a bound control, it can't be in the control source.
That is why you are not able to update. The control source is where the
control is bound to the field.
Have you tried making the call in the immediate window?

"Matt Dawson" wrote:

Makes no difference. Any other thoughts?

Cant seem to make it work. If i take the combo box out it works, but not
with it. However, i can't set it without the combo box or set the control
value as CurrentUser() as this then changes and has a large effect on the
tables and report data. This does not update with that as the control value

Matt

"Klatuu" wrote:

Remove the = sign

"Matt Dawson" wrote:

I currently have combo box in which agents select their own ID when they are
completing a quote.
THey also have their own log ins and I was wondering if it was possible to
set this combo box default value to the current user.
I have tried entering =CurrentUser() in the default value but this does not
work

Any ideas?
Thanks,
Matt

I don't think so

"Matt Dawson" wrote:

Could it be because I ahve another unbound field.
The current unbound field is Request ID and the agents go in and search by
this to find the correct record??

"Klatuu" wrote:



"Matt Dawson" wrote:

Not sure why I can't make it work when the combo is bound but for some reason
it cannot.
I don't understand this, it worked for me in 2003, what version are you using?

I need to to be bound as this is the only way i can make the reports update
with the correct agent ID. This is needed

Not really, If you want to try this approach, I can show you a VBA solution
that will take care of that. It is done all the time.


It does not need to be a combo, but it does need to be bound to Agent ID in
order for the reports to work. Is it definitely CurrentUser() that I need to
enter?

CurrentUser() is correct. However, if you are not using Access WorkGroup
Security, every user will return "admin"


"Klatuu" wrote:

It appears you are not familiar with VBA if you don't understand the
immediate window.

As I said before, it worked for me in both a bound and an unbound combo.

Just a thought, If the user is going to select his own ID, then is it
necessary to have a combo? Would they be able to select another ID? If they
are always going to use their own ID, then a text box with CurrentUser() in
the control source should do just fine. If not, then you could make the
combo unbound, put the CurrentUser() in the default value, create a text box
(it could be invisible) with the control source of =MyCombo, and Make the
text box the bound control.

"Matt Dawson" wrote:

If the control is unbound, then the theory works

However, the reports then do not work as they do not recognise the unbound
field and do not list the sent quotes by agent but simply as a blanks!

How do you mean "making the call in the immediate window"??

Matt

"Klatuu" wrote:

I tested it here (2003) on both a bound and an unbound combo using it as the
default value. If it is a bound control, it can't be in the control source.
That is why you are not able to update. The control source is where the
control is bound to the field.
Have you tried making the call in the immediate window?

"Matt Dawson" wrote:

Makes no difference. Any other thoughts?

Cant seem to make it work. If i take the combo box out it works, but not
with it. However, i can't set it without the combo box or set the control
value as CurrentUser() as this then changes and has a large effect on the
tables and report data. This does not update with that as the control value

Matt

"Klatuu" wrote:

Remove the = sign

"Matt Dawson" wrote:

I currently have combo box in which agents select their own ID when they are
completing a quote.
THey also have their own log ins and I was wondering if it was possible to
set this combo box default value to the current user.
I have tried entering =CurrentUser() in the default value but this does not
work

Any ideas?
Thanks,
Matt

Ok, yes you are right, it makes no difference.

Is it possible to do it all via VBA!
I think i posted the answers to your questions in e-mail 11!

"Klatuu" wrote:

I don't think so

"Matt Dawson" wrote:

Could it be because I ahve another unbound field.
The current unbound field is Request ID and the agents go in and search by
this to find the correct record??

"Klatuu" wrote:



"Matt Dawson" wrote:

Not sure why I can't make it work when the combo is bound but for some reason
it cannot.
I don't understand this, it worked for me in 2003, what version are you using?

I need to to be bound as this is the only way i can make the reports update
with the correct agent ID. This is needed

Not really, If you want to try this approach, I can show you a VBA solution
that will take care of that. It is done all the time.


It does not need to be a combo, but it does need to be bound to Agent ID in
order for the reports to work. Is it definitely CurrentUser() that I need to
enter?

CurrentUser() is correct. However, if you are not using Access WorkGroup
Security, every user will return "admin"


"Klatuu" wrote:

It appears you are not familiar with VBA if you don't understand the
immediate window.

As I said before, it worked for me in both a bound and an unbound combo.

Just a thought, If the user is going to select his own ID, then is it
necessary to have a combo? Would they be able to select another ID? If they
are always going to use their own ID, then a text box with CurrentUser() in
the control source should do just fine. If not, then you could make the
combo unbound, put the CurrentUser() in the default value, create a text box
(it could be invisible) with the control source of =MyCombo, and Make the
text box the bound control.

"Matt Dawson" wrote:

If the control is unbound, then the theory works

However, the reports then do not work as they do not recognise the unbound
field and do not list the sent quotes by agent but simply as a blanks!

How do you mean "making the call in the immediate window"??

Matt

"Klatuu" wrote:

I tested it here (2003) on both a bound and an unbound combo using it as the
default value. If it is a bound control, it can't be in the control source.
That is why you are not able to update. The control source is where the
control is bound to the field.
Have you tried making the call in the immediate window?

"Matt Dawson" wrote:

Makes no difference. Any other thoughts?

Cant seem to make it work. If i take the combo box out it works, but not
with it. However, i can't set it without the combo box or set the control
value as CurrentUser() as this then changes and has a large effect on the
tables and report data. This does not update with that as the control value

Matt

"Klatuu" wrote:

Remove the = sign

"Matt Dawson" wrote:

I currently have combo box in which agents select their own ID when they are
completing a quote.
THey also have their own log ins and I was wondering if it was possible to
set this combo box default value to the current user.
I have tried entering =CurrentUser() in the default value but this does not
work

Any ideas?
Thanks,
Matt

Yes you did, thanks

I'll get this back to you today if I can. I am pretty busy, so it may be
tomorrow AM (USA Central). Hope that wont be a problem.

"Matt Dawson" wrote:

Ok, yes you are right, it makes no difference.

Is it possible to do it all via VBA!
I think i posted the answers to your questions in e-mail 11!

"Klatuu" wrote:

I don't think so

"Matt Dawson" wrote:

Could it be because I ahve another unbound field.
The current unbound field is Request ID and the agents go in and search by
this to find the correct record??

"Klatuu" wrote:



"Matt Dawson" wrote:

Not sure why I can't make it work when the combo is bound but for some reason
it cannot.
I don't understand this, it worked for me in 2003, what version are you using?

I need to to be bound as this is the only way i can make the reports update
with the correct agent ID. This is needed

Not really, If you want to try this approach, I can show you a VBA solution
that will take care of that. It is done all the time.


It does not need to be a combo, but it does need to be bound to Agent ID in
order for the reports to work. Is it definitely CurrentUser() that I need to
enter?

CurrentUser() is correct. However, if you are not using Access WorkGroup
Security, every user will return "admin"


"Klatuu" wrote:

It appears you are not familiar with VBA if you don't understand the
immediate window.

As I said before, it worked for me in both a bound and an unbound combo.

Just a thought, If the user is going to select his own ID, then is it
necessary to have a combo? Would they be able to select another ID? If they
are always going to use their own ID, then a text box with CurrentUser() in
the control source should do just fine. If not, then you could make the
combo unbound, put the CurrentUser() in the default value, create a text box
(it could be invisible) with the control source of =MyCombo, and Make the
text box the bound control.

"Matt Dawson" wrote:

If the control is unbound, then the theory works

However, the reports then do not work as they do not recognise the unbound
field and do not list the sent quotes by agent but simply as a blanks!

How do you mean "making the call in the immediate window"??

Matt

"Klatuu" wrote:

I tested it here (2003) on both a bound and an unbound combo using it as the
default value. If it is a bound control, it can't be in the control source.
That is why you are not able to update. The control source is where the
control is bound to the field.
Have you tried making the call in the immediate window?

"Matt Dawson" wrote:

Makes no difference. Any other thoughts?

Cant seem to make it work. If i take the combo box out it works, but not
with it. However, i can't set it without the combo box or set the control
value as CurrentUser() as this then changes and has a large effect on the
tables and report data. This does not update with that as the control value

Matt

"Klatuu" wrote:

Remove the = sign

"Matt Dawson" wrote:

I currently have combo box in which agents select their own ID when they are
completing a quote.
THey also have their own log ins and I was wondering if it was possible to
set this combo box default value to the current user.
I have tried entering =CurrentUser() in the default value but this does not
work

Any ideas?
Thanks,
Matt

That is fine,

I think I can allow you the time, as you are helping me out!

"Klatuu" wrote:

Yes you did, thanks

I'll get this back to you today if I can. I am pretty busy, so it may be
tomorrow AM (USA Central). Hope that wont be a problem.

"Matt Dawson" wrote:

Ok, yes you are right, it makes no difference.

Is it possible to do it all via VBA!
I think i posted the answers to your questions in e-mail 11!

"Klatuu" wrote:

I don't think so

"Matt Dawson" wrote:

Could it be because I ahve another unbound field.
The current unbound field is Request ID and the agents go in and search by
this to find the correct record??

"Klatuu" wrote:



"Matt Dawson" wrote:

Not sure why I can't make it work when the combo is bound but for some reason
it cannot.
I don't understand this, it worked for me in 2003, what version are you using?

I need to to be bound as this is the only way i can make the reports update
with the correct agent ID. This is needed

Not really, If you want to try this approach, I can show you a VBA solution
that will take care of that. It is done all the time.


It does not need to be a combo, but it does need to be bound to Agent ID in
order for the reports to work. Is it definitely CurrentUser() that I need to
enter?

CurrentUser() is correct. However, if you are not using Access WorkGroup
Security, every user will return "admin"


"Klatuu" wrote:

It appears you are not familiar with VBA if you don't understand the
immediate window.

As I said before, it worked for me in both a bound and an unbound combo.

Just a thought, If the user is going to select his own ID, then is it
necessary to have a combo? Would they be able to select another ID? If they
are always going to use their own ID, then a text box with CurrentUser() in
the control source should do just fine. If not, then you could make the
combo unbound, put the CurrentUser() in the default value, create a text box
(it could be invisible) with the control source of =MyCombo, and Make the
text box the bound control.

"Matt Dawson" wrote:

If the control is unbound, then the theory works

However, the reports then do not work as they do not recognise the unbound
field and do not list the sent quotes by agent but simply as a blanks!

How do you mean "making the call in the immediate window"??

Matt

"Klatuu" wrote:

I tested it here (2003) on both a bound and an unbound combo using it as the
default value. If it is a bound control, it can't be in the control source.
That is why you are not able to update. The control source is where the
control is bound to the field.
Have you tried making the call in the immediate window?

"Matt Dawson" wrote:

Makes no difference. Any other thoughts?

Cant seem to make it work. If i take the combo box out it works, but not
with it. However, i can't set it without the combo box or set the control
value as CurrentUser() as this then changes and has a large effect on the
tables and report data. This does not update with that as the control value

Matt

"Klatuu" wrote:

Remove the = sign

"Matt Dawson" wrote:

I currently have combo box in which agents select their own ID when they are
completing a quote.
THey also have their own log ins and I was wondering if it was possible to
set this combo box default value to the current user.
I have tried entering =CurrentUser() in the default value but this does not
work

Any ideas?
Thanks,
Matt

Okay, let's try this approach. First, we will set the current user in the
Load event of the form. We also have to make sure the Agent is in the Agents
table, so we will check for that.
We will make the combo unbound.
We will add a text box and bind it to [Agent ID] We will name it txtAgentID
(Notice I removed the space in Agent ID and added txt to the beginning.
Spaces are not good in names. The require bracketing to be recognized. Try
to limit names to letters, numbers, and the underscore. The txt means it is
a text box.

Form Load Event:

Dim strUser As string
'Check to see if the Agent is in the Agent table
'And Put it in the Comb

strUser = CurrentUser
If IsNull(DLookup("[Agent ID]", "Agents", "[Agent ID] = '" & strUser &
"'")) Then
If MsgBox strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then
CurrentDb.Execute ("INSERT INTO Agents ([Agent ID]) " _
& "VALUES ('" & strUser & "');"), dbFailOnError
Me.[Agent ID].Requery
Me.[Agent ID] = strUser
Else
Me.[Agent ID] = Null
End If
Esle
Me.[Agent ID] = strUser
End If

Me.[Agent ID] = CurrentUser()

In the Form Current event, Put the value of the combo in the text box for
new records:

If Me.NewRecord Then
Me.txtAgentID = Me.[Agent ID]
End If


"Matt Dawson" wrote:

That is fine,

I think I can allow you the time, as you are helping me out!

"Klatuu" wrote:

Yes you did, thanks

I'll get this back to you today if I can. I am pretty busy, so it may be
tomorrow AM (USA Central). Hope that wont be a problem.

"Matt Dawson" wrote:

Ok, yes you are right, it makes no difference.

Is it possible to do it all via VBA!
I think i posted the answers to your questions in e-mail 11!

"Klatuu" wrote:

I don't think so

"Matt Dawson" wrote:

Could it be because I ahve another unbound field.
The current unbound field is Request ID and the agents go in and search by
this to find the correct record??

"Klatuu" wrote:



"Matt Dawson" wrote:

Not sure why I can't make it work when the combo is bound but for some reason
it cannot.
I don't understand this, it worked for me in 2003, what version are you using?

I need to to be bound as this is the only way i can make the reports update
with the correct agent ID. This is needed

Not really, If you want to try this approach, I can show you a VBA solution
that will take care of that. It is done all the time.


It does not need to be a combo, but it does need to be bound to Agent ID in
order for the reports to work. Is it definitely CurrentUser() that I need to
enter?

CurrentUser() is correct. However, if you are not using Access WorkGroup
Security, every user will return "admin"


"Klatuu" wrote:

It appears you are not familiar with VBA if you don't understand the
immediate window.

As I said before, it worked for me in both a bound and an unbound combo.

Just a thought, If the user is going to select his own ID, then is it
necessary to have a combo? Would they be able to select another ID? If they
are always going to use their own ID, then a text box with CurrentUser() in
the control source should do just fine. If not, then you could make the
combo unbound, put the CurrentUser() in the default value, create a text box
(it could be invisible) with the control source of =MyCombo, and Make the
text box the bound control.

"Matt Dawson" wrote:

If the control is unbound, then the theory works

However, the reports then do not work as they do not recognise the unbound
field and do not list the sent quotes by agent but simply as a blanks!

How do you mean "making the call in the immediate window"??

Matt

"Klatuu" wrote:

I tested it here (2003) on both a bound and an unbound combo using it as the
default value. If it is a bound control, it can't be in the control source.
That is why you are not able to update. The control source is where the
control is bound to the field.
Have you tried making the call in the immediate window?

"Matt Dawson" wrote:

Makes no difference. Any other thoughts?

Cant seem to make it work. If i take the combo box out it works, but not
with it. However, i can't set it without the combo box or set the control
value as CurrentUser() as this then changes and has a large effect on the
tables and report data. This does not update with that as the control value

Matt

"Klatuu" wrote:

Remove the = sign

"Matt Dawson" wrote:

I currently have combo box in which agents select their own ID when they are
completing a quote.
THey also have their own log ins and I was wondering if it was possible to
set this combo box default value to the current user.
I have tried entering =CurrentUser() in the default value but this does not
work

Any ideas?
Thanks,
Matt

Ok, I have entered that into VBA but we come up with a compile syntax error
with the following text in red:

If IsNull(DLookup("[Agent ID]", "Agents", "[Agent ID] = '" & strUser &
"'")) Then
If MsgBox strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then

Matt
"Klatuu" wrote:

Okay, let's try this approach. First, we will set the current user in the
Load event of the form. We also have to make sure the Agent is in the Agents
table, so we will check for that.
We will make the combo unbound.
We will add a text box and bind it to [Agent ID] We will name it txtAgentID
(Notice I removed the space in Agent ID and added txt to the beginning.
Spaces are not good in names. The require bracketing to be recognized. Try
to limit names to letters, numbers, and the underscore. The txt means it is
a text box.

Form Load Event:

Dim strUser As string
'Check to see if the Agent is in the Agent table
'And Put it in the Comb

strUser = CurrentUser
If IsNull(DLookup("[Agent ID]", "Agents", "[Agent ID] = '" & strUser &
"'")) Then
If MsgBox strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then
CurrentDb.Execute ("INSERT INTO Agents ([Agent ID]) " _
& "VALUES ('" & strUser & "');"), dbFailOnError
Me.[Agent ID].Requery
Me.[Agent ID] = strUser
Else
Me.[Agent ID] = Null
End If
Esle
Me.[Agent ID] = strUser
End If

Me.[Agent ID] = CurrentUser()

In the Form Current event, Put the value of the combo in the text box for
new records:

If Me.NewRecord Then
Me.txtAgentID = Me.[Agent ID]
End If


"Matt Dawson" wrote:

That is fine,

I think I can allow you the time, as you are helping me out!

"Klatuu" wrote:

Yes you did, thanks

I'll get this back to you today if I can. I am pretty busy, so it may be
tomorrow AM (USA Central). Hope that wont be a problem.

"Matt Dawson" wrote:

Ok, yes you are right, it makes no difference.

Is it possible to do it all via VBA!
I think i posted the answers to your questions in e-mail 11!

"Klatuu" wrote:

I don't think so

"Matt Dawson" wrote:

Could it be because I ahve another unbound field.
The current unbound field is Request ID and the agents go in and search by
this to find the correct record??

"Klatuu" wrote:



"Matt Dawson" wrote:

Not sure why I can't make it work when the combo is bound but for some reason
it cannot.
I don't understand this, it worked for me in 2003, what version are you using?

I need to to be bound as this is the only way i can make the reports update
with the correct agent ID. This is needed

Not really, If you want to try this approach, I can show you a VBA solution
that will take care of that. It is done all the time.


It does not need to be a combo, but it does need to be bound to Agent ID in
order for the reports to work. Is it definitely CurrentUser() that I need to
enter?

CurrentUser() is correct. However, if you are not using Access WorkGroup
Security, every user will return "admin"


"Klatuu" wrote:

It appears you are not familiar with VBA if you don't understand the
immediate window.

As I said before, it worked for me in both a bound and an unbound combo.

Just a thought, If the user is going to select his own ID, then is it
necessary to have a combo? Would they be able to select another ID? If they
are always going to use their own ID, then a text box with CurrentUser() in
the control source should do just fine. If not, then you could make the
combo unbound, put the CurrentUser() in the default value, create a text box
(it could be invisible) with the control source of =MyCombo, and Make the
text box the bound control.

"Matt Dawson" wrote:

If the control is unbound, then the theory works

However, the reports then do not work as they do not recognise the unbound
field and do not list the sent quotes by agent but simply as a blanks!

How do you mean "making the call in the immediate window"??

Matt

"Klatuu" wrote:

I tested it here (2003) on both a bound and an unbound combo using it as the
default value. If it is a bound control, it can't be in the control source.
That is why you are not able to update. The control source is where the
control is bound to the field.
Have you tried making the call in the immediate window?

"Matt Dawson" wrote:

Makes no difference. Any other thoughts?

Cant seem to make it work. If i take the combo box out it works, but not
with it. However, i can't set it without the combo box or set the control
value as CurrentUser() as this then changes and has a large effect on the
tables and report data. This does not update with that as the control value

Matt

"Klatuu" wrote:

Remove the = sign

"Matt Dawson" wrote:

I currently have combo box in which agents select their own ID when they are
completing a quote.
THey also have their own log ins and I was wondering if it was possible to
set this combo box default value to the current user.
I have tried entering =CurrentUser() in the default value but this does not
work

Any ideas?
Thanks,
Matt

Okay, I copied into my VBA editor and here is what it is.
When you cut and pasted, it put is some carriage returns.

This line needs to be all one one line. Put your cursor at the end of the
first line and hit delete, then hit space. that will leave If MsgBox part
red.

If IsNull(DLookup("[Agent ID]", "Agents", "[Agent ID] = '" & strUser &
"'")) Then

The MsgBox is a syntax error
If MsgBox strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then
Should be
If MsgBox = strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then

My bad.



"Matt Dawson" wrote:

Ok, I have entered that into VBA but we come up with a compile syntax error
with the following text in red:

If IsNull(DLookup("[Agent ID]", "Agents", "[Agent ID] = '" & strUser &
"'")) Then
If MsgBox strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then

Matt
"Klatuu" wrote:

Okay, let's try this approach. First, we will set the current user in the
Load event of the form. We also have to make sure the Agent is in the Agents
table, so we will check for that.
We will make the combo unbound.
We will add a text box and bind it to [Agent ID] We will name it txtAgentID
(Notice I removed the space in Agent ID and added txt to the beginning.
Spaces are not good in names. The require bracketing to be recognized. Try
to limit names to letters, numbers, and the underscore. The txt means it is
a text box.

Form Load Event:

Dim strUser As string
'Check to see if the Agent is in the Agent table
'And Put it in the Comb

strUser = CurrentUser
If IsNull(DLookup("[Agent ID]", "Agents", "[Agent ID] = '" & strUser &
"'")) Then
If MsgBox strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then
CurrentDb.Execute ("INSERT INTO Agents ([Agent ID]) " _
& "VALUES ('" & strUser & "');"), dbFailOnError
Me.[Agent ID].Requery
Me.[Agent ID] = strUser
Else
Me.[Agent ID] = Null
End If
Esle
Me.[Agent ID] = strUser
End If

Me.[Agent ID] = CurrentUser()

In the Form Current event, Put the value of the combo in the text box for
new records:

If Me.NewRecord Then
Me.txtAgentID = Me.[Agent ID]
End If


"Matt Dawson" wrote:

That is fine,

I think I can allow you the time, as you are helping me out!

"Klatuu" wrote:

Yes you did, thanks

I'll get this back to you today if I can. I am pretty busy, so it may be
tomorrow AM (USA Central). Hope that wont be a problem.

"Matt Dawson" wrote:

Ok, yes you are right, it makes no difference.

Is it possible to do it all via VBA!
I think i posted the answers to your questions in e-mail 11!

"Klatuu" wrote:

I don't think so

"Matt Dawson" wrote:

Could it be because I ahve another unbound field.
The current unbound field is Request ID and the agents go in and search by
this to find the correct record??

"Klatuu" wrote:



"Matt Dawson" wrote:

Not sure why I can't make it work when the combo is bound but for some reason
it cannot.
I don't understand this, it worked for me in 2003, what version are you using?

I need to to be bound as this is the only way i can make the reports update
with the correct agent ID. This is needed

Not really, If you want to try this approach, I can show you a VBA solution
that will take care of that. It is done all the time.


It does not need to be a combo, but it does need to be bound to Agent ID in
order for the reports to work. Is it definitely CurrentUser() that I need to
enter?

CurrentUser() is correct. However, if you are not using Access WorkGroup
Security, every user will return "admin"


"Klatuu" wrote:

It appears you are not familiar with VBA if you don't understand the
immediate window.

As I said before, it worked for me in both a bound and an unbound combo.

Just a thought, If the user is going to select his own ID, then is it
necessary to have a combo? Would they be able to select another ID? If they
are always going to use their own ID, then a text box with CurrentUser() in
the control source should do just fine. If not, then you could make the
combo unbound, put the CurrentUser() in the default value, create a text box
(it could be invisible) with the control source of =MyCombo, and Make the
text box the bound control.

"Matt Dawson" wrote:

If the control is unbound, then the theory works

However, the reports then do not work as they do not recognise the unbound
field and do not list the sent quotes by agent but simply as a blanks!

How do you mean "making the call in the immediate window"??

Matt

"Klatuu" wrote:

I tested it here (2003) on both a bound and an unbound combo using it as the
default value. If it is a bound control, it can't be in the control source.
That is why you are not able to update. The control source is where the
control is bound to the field.
Have you tried making the call in the immediate window?

"Matt Dawson" wrote:

Makes no difference. Any other thoughts?

Cant seem to make it work. If i take the combo box out it works, but not
with it. However, i can't set it without the combo box or set the control
value as CurrentUser() as this then changes and has a large effect on the
tables and report data. This does not update with that as the control value

Matt

"Klatuu" wrote:

Remove the = sign

"Matt Dawson" wrote:

I currently have combo box in which agents select their own ID when they are
completing a quote.
THey also have their own log ins and I was wondering if it was possible to
set this combo box default value to the current user.
I have tried entering =CurrentUser() in the default value but this does not
work

Any ideas?
Thanks,
Matt

OK,

Well the next line is in red:

If MsgBox = strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then

But it also comes up with anpther syntax error!

"Klatuu" wrote:

Okay, I copied into my VBA editor and here is what it is.
When you cut and pasted, it put is some carriage returns.

This line needs to be all one one line. Put your cursor at the end of the
first line and hit delete, then hit space. that will leave If MsgBox part
red.

If IsNull(DLookup("[Agent ID]", "Agents", "[Agent ID] = '" & strUser &
"'")) Then

The MsgBox is a syntax error
If MsgBox strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then
Should be
If MsgBox = strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then

My bad.



"Matt Dawson" wrote:

Ok, I have entered that into VBA but we come up with a compile syntax error
with the following text in red:

If IsNull(DLookup("[Agent ID]", "Agents", "[Agent ID] = '" & strUser &
"'")) Then
If MsgBox strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then

Matt
"Klatuu" wrote:

Okay, let's try this approach. First, we will set the current user in the
Load event of the form. We also have to make sure the Agent is in the Agents
table, so we will check for that.
We will make the combo unbound.
We will add a text box and bind it to [Agent ID] We will name it txtAgentID
(Notice I removed the space in Agent ID and added txt to the beginning.
Spaces are not good in names. The require bracketing to be recognized. Try
to limit names to letters, numbers, and the underscore. The txt means it is
a text box.

Form Load Event:

Dim strUser As string
'Check to see if the Agent is in the Agent table
'And Put it in the Comb

strUser = CurrentUser
If IsNull(DLookup("[Agent ID]", "Agents", "[Agent ID] = '" & strUser &
"'")) Then
If MsgBox strUser & " Is Not in the Agents Table" & vbNewLine & _
"Add This User", vbYesNo + vbQuestion) = vbYes Then
CurrentDb.Execute ("INSERT INTO Agents ([Agent ID]) " _
& "VALUES ('" & strUser & "');"), dbFailOnError
Me.[Agent ID].Requery
Me.[Agent ID] = strUser
Else
Me.[Agent ID] = Null
End If
Esle
Me.[Agent ID] = strUser
End If

Me.[Agent ID] = CurrentUser()

In the Form Current event, Put the value of the combo in the text box for
new records:

If Me.NewRecord Then
Me.txtAgentID = Me.[Agent ID]
End If


"Matt Dawson" wrote:

That is fine,

I think I can allow you the time, as you are helping me out!

"Klatuu" wrote:

Yes you did, thanks

I'll get this back to you today if I can. I am pretty busy, so it may be
tomorrow AM (USA Central). Hope that wont be a problem.

"Matt Dawson" wrote:

Ok, yes you are right, it makes no difference.

Is it possible to do it all via VBA!
I think i posted the answers to your questions in e-mail 11!

"Klatuu" wrote:

I don't think so

"Matt Dawson" wrote:

Could it be because I ahve another unbound field.
The current unbound field is Request ID and the agents go in and search by
this to find the correct record??

"Klatuu" wrote:



"Matt Dawson" wrote:

Not sure why I can't make it work when the combo is bound but for some reason
it cannot.
I don't understand this, it worked for me in 2003, what version are you using?

I need to to be bound as this is the only way i can make the reports update
with the correct agent ID. This is needed

Not really, If you want to try this approach, I can show you a VBA solution
that will take care of that. It is done all the time.


It does not need to be a combo, but it does need to be bound to Agent ID in
order for the reports to work. Is it definitely CurrentUser() that I need to
enter?

CurrentUser() is correct. However, if you are not using Access WorkGroup
Security, every user will return "admin"


"Klatuu" wrote:

It appears you are not familiar with VBA if you don't understand the
immediate window.

As I said before, it worked for me in both a bound and an unbound combo.

Just a thought, If the user is going to select his own ID, then is it
necessary to have a combo? Would they be able to select another ID? If they
are always going to use their own ID, then a text box with CurrentUser() in
the control source should do just fine. If not, then you could make the
combo unbound, put the CurrentUser() in the default value, create a text box
(it could be invisible) with the control source of =MyCombo, and Make the
text box the bound control.

"Matt Dawson" wrote:

If the control is unbound, then the theory works

However, the reports then do not work as they do not recognise the unbound
field and do not list the sent quotes by agent but simply as a blanks!

How do you mean "making the call in the immediate window"??

Matt

"Klatuu" wrote:

I tested it here (2003) on both a bound and an unbound combo using it as the
default value. If it is a bound control, it can't be in the control source.
That is why you are not able to update. The control source is where the
control is bound to the field.
Have you tried making the call in the immediate window?

"Matt Dawson" wrote:

Makes no difference. Any other thoughts?

Cant seem to make it work. If i take the combo box out it works, but not
with it. However, i can't set it without the combo box or set the control
value as CurrentUser() as this then changes and has a large effect on the
tables and report data. This does not update with that as the control value

Matt

"Klatuu" wrote:

Remove the = sign

"Matt Dawson" wrote:

I currently have combo box in which agents select their own ID when they are
completing a quote.
THey also have their own log ins and I was wondering if it was possible to
set this combo box default value to the current user.
I have tried entering =CurrentUser() in the default value but this does not
work

Any ideas?
Thanks,
Matt