G
Guest
Sorry, that last error message was incorrect
It actually reads - Expected: Then or Go To
Matt
It actually reads - Expected: Then or Go To
Matt
Matt Dawson said: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 said: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 said: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
:
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
:
That is fine,
I think I can allow you the time, as you are helping me out!
:
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.
:
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!
:
I don't think so
:
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??
:
:
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"
:
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.
:
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
:
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?
:
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
:
Remove the = sign
:
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