Update query (1 Viewer)

[CoffeeGuru]

I am having a problem, where when a user selects a user name from a dropdown box (cboEmployee) an After Update event occurs
DoCmd.RunMacro "user"
Me.txtPassword.SetFocus

The Macro "user" should do the following:
Open Query "Empty_User"
DELETE Current_User.User AS Expr1, Current_User.User
FROM [Current_User]
WHERE (((Current_User.User) Like "*"));


Open Query "Current_User"
PARAMETERS cboEmployee Short;
INSERT INTO [Current_User] ( [User] )
SELECT [cboEmployee] AS Expr1
FROM [Current_User];

Open Query "Current_User_2"
UPDATE (User_Data INNER JOIN [Current_User] ON User_Data.lngEmpID = Current_User.User) INNER JOIN tblCountry ON User_Data.strEmpName = tblCountry.Country SET [Current_User].UserName = [strEmpName], [Current_User].CountryCode = [Country Code];

The table Current_User does clear down but then I get a message box asking me to Enter a Parameter Value for "cboEmployee"
as cboEmployee is the name of the combobox I was under the impression that the contents of the combobox would be passed to the query.
I should probably state as this point that the tables are on SQL Server, if it makes any difference. If I press Cancel I get the following box
Macro Name: User
Condition:
Action Name: OpenQuery
Arguments: Current_User, Datasheet, Edit
Error Number: 3021

Can anyone know tell me what I missing here?


Update
I forgot to say that cboEmployee also has this Row Source:
SELECT User_Data.lngEmpID AS Expr1, User_Data.CountryCode, User_Data.strEmpName AS Expr2 FROM User_Data;
Row Source Type: Table/Query

 

[pbaldy]

You have to use the full form reference in a query:

http://access.mvps.org/access/forms/frm0031.htm

 

[CoffeeGuru]

Hi Paul

Thanks for your comments.
I am at a complete loss though where this full form reference is missing the Empty_User macro works so where/how do I change the other 2 queries and what is the difference?
Apologies for my ignorance...

Martin

 

[pbaldy]

SELECT [cboEmployee] AS Expr1

 

[CoffeeGuru]

SELECT [cboEmployee] AS Expr1

OK, I have changed it to:

PARAMETERS cboEmployee Short;
INSERT INTO [Current_User] ( [User] )
SELECT frmLogon.cboEmployee AS Expr1
FROM [Current_User];

I still get a message box to enter a value for cboEmployee

If I also change PARAMETERS cboEmployee Short;
to PARAMETERS frmLogon.cboEmployee Short;

I get a message box to enter a value for frmLogon.cboEmployee

This is so frustrating.....:banghead:

 

[pbaldy]

The first part wasn't optional:

Forms!frmLogon.cboEmployee

 

[CoffeeGuru]

Yes, I see that was wrong,
However this has made no difference

PARAMETERS cboEmployee Short;
INSERT INTO [Current_User] ( [User] )
SELECT forms!frmLogon.cboEmployee AS Expr1
FROM [Current_User];

I still get a text box asking for me to Enter Parameter Value for cboEmployee

Even if I put a valid value in there, just to force the issue, i.e. 6 the table still does not get populated

 

[pbaldy]

It's still in the parameters clause, which is optional in this case. Try

INSERT INTO [Current_User] ( [User] )
VALUES (forms!frmLogon.cboEmployee)

 

[CoffeeGuru]

I must be doing something really stupid here as I still get the same message box

PARAMETERS cboEmployee Short;
INSERT INTO [Current_User] ( [User] )
Values(forms!frmLogon.cboEmployee);

 

[CoffeeGuru]

hang on me being dim...
remove the parameter statement and I now get through but the table is still not populated.

Actually thats not entirely true

Current_User is being populated
its the 3rd and final Query in the Macro that is not working.

I've changed this based on your insight

UPDATE (User_Data
INNER JOIN [Current_User]
ON User_Data.lngEmpID = Current_User.User)
INNER JOIN tblCountry
ON User_Data.strEmpName = tblCountry.Country
SET [Current_User].UserName = [strEmpName],
[Current_User].CountryCode = [Country Code];

To this
UPDATE (User_Data
INNER JOIN [Current_User]
ON User_Data.lngEmpID = Current_User.User)
INNER JOIN tblCountry
ON User_Data.strEmpName = tblCountry.Country
SET [Current_User].[User] = [forms]![frmLogon]![cboEmployee],
[Current_User].UserName = [strEmpName],
[Current_User].CountryCode = [Country Code];

I can appreachiate that you do not know the values held in User_Data

so
lngEmpID = integer (example 6)
strEmpNam = various european countries (example United Kingdom)
CountryCode = 2 char code (example GB)

 

[pbaldy]

Can you post the db here?

 

[CoffeeGuru]

Hi Paul I've uploaded a version.

 

[pbaldy]

Sorry, I failed to mention it wouldn't work for me with linked tables. The append query here still uses the SELECT syntax. Since there are no records in that table (correct?), it wouldn't append any. Have you tried:

INSERT INTO [Current_User] ( [User] )
VALUES (forms!frmLogon.cboEmployee)

 

[CoffeeGuru]

Hi Paul

Correct, the table at this point is now empty

Assuming that you are looking at my db
are you talking about Query 0_Current_User_2

UPDATE (User_Data
INNER JOIN [Current_User]
ON User_Data.lngEmpID = Current_User.User)
INNER JOIN tblCountry
ON User_Data.strEmpName = tblCountry.Country
SET [Current_User].[User] = [forms]![frmLogon]![cboEmployee], [Current_User].UserName = [strEmpName],
[Current_User].CountryCode = [Country Code];

Do I just change the top line?

INSERT INTO (User_Data
INNER JOIN [Current_User]
ON User_Data.lngEmpID = Current_User.User)
INNER JOIN tblCountry
ON User_Data.strEmpName = tblCountry.Country
SET [Current_User].[User] = [forms]![frmLogon]![cboEmployee], [Current_User].UserName = [strEmpName],
[Current_User].CountryCode = [Country Code];

Apologies if you feel you are talking to a dim wit.

M.

 

[CoffeeGuru]

OK
I was really stupid yesturday and for the last month
This code works
A: it should have been an append query not an update query
B: I had a wrong table in the query
Paul Thanks for your time, I would not have seen it if I didn't set my self up as a clown.

This is my working query:
INSERT INTO [Current_User] ( [User], UserName, CountryCode )
SELECT User_Data.lngEmpID, User_Data.strEmpName, User_Data.CountryCode
FROM User_Data
INNER JOIN [Current_User] ON User_Data.lngEmpID = Current_User.User;

M.

 

[pbaldy]

Glad you got it sorted out Martin, and all while I slept!