Updating multiple records from a lookup list 1

[cjjc]

Hi
i need to add some additional fields to a group of records.
the group of records is produced by a parameter query, which currently ive sent to maketable as a temp store.
the next step i need to do is have the user select two more criteria - a contractor name and user id - then write this info to each record on the tempstore. the problem i have is that the new criteria must be selected from lookup lists and i cant get my head round how to do this - can i use a form? how do i store them as a variable?
please help!
thanks
c

 

[SQLBI]

Hi,

Yes you can use a form and i'd suggest the following method.

1. On your form create 2 ComboBoxes, cboContractor and cboUser.

2. Set the data source for each cbo to either a query or the table where the info is stored e.g.

SELECT DISTINCT Contractor_Name FROM tblContractor

.

3. Create 2 new global variables and 2 VBA functions;

Public strContName As String
Public strUserID As String

Public Function ContName() As String
     ContName = strContName
End Function

Public Function UserID() As String
     UserID = strUserID
End Function

4. Create your Append Query e.g.

 INSERT INTO tblMarketable (Contractor_Name, UserID)
VALUES (ContName(), UserID());

5. On your form, create a CommandButton and place this code into the Click() event.

strContName = Me.cboContractor
strUserID = Me.cboUser

DoCmd.OpenQuery "qryYourQueryName"

Hope all this makes sence, let me know if you have any problems.




Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[cjjc]

Leigh, thats fantastic, i can see the logic in this.
re no. 4 - can i force the values in even if a field of that name doesnt already exist in the table?
re no 5 - is this command button to be clicked once the user has selected the values? i intend to move the user from the form to view a report next via a button so could these two events be combined?
thanks v much for your help.
c
:-D

 

[SQLBI]

c,

4. An append query will insert a new record into your table, so my example will always create a new record with ContractorName and UserID.

If a record already exists, you can use an UPDATE query to change either ContractName or UserID of an existing record.

5. If you want the report to open just add the code to open the report after the

DoCmd.OpenQuery

.

Anything else, let me know.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[cjjc]

thanks. i shall go try.......c

 

[cjjc]

Hello again
have managed to set up the code as described by Leigh, however although everything appears to be working, the variables do not appear to be being passed to the update query.

The final set up i have is...a macro which runs a query to select the records to be updated, then adds the additional fields to this table that the variables will be input to. This all works fine.

The macro then loads the form for the input of the two variables i require from their lookup lists. The user is presented with 2 Combo boxes, one for UserID and the other for Contractor name.
Within the forms module i have set up the variables and procedures as described by Leigh, plus the command button and click event code. I then use the DoCmd to run the update query to pass the variables into the table. The SQL behind this query is as follows....

UPDATE tblMyTable SET tblMytable.UserID = UserID(), tblMytable.Contractor = Contname();

Once ive selected the User ID and Contname, then click the command button, the update query runs, but i get an error 'undefined function 'UserID' in expression' (i have taken user id out and get the same error on Cont name)

I assume this is because either the query can't see the variables as they are within the form (however all public functions and form still open???) or have i just set up the update query wrong?

Please help! im not very familiar with using variables like this. Thanks in anticipation
C

 

[SQLBI]

Hi,

"have managed to set up the code as described by Leigh, however although everything appears to be working, the variables do not appear to be being passed to the update query."

To get the functions to work, you need to pass values to the 2 public variables, so for example

strContName = "Bill"

Then when you run your query, the function will use this variable and the query will use the function.

Hope this helps you out, sorry for missing this off my original post.

"Once ive selected the User ID and Contname, then click the command button, the update query runs, but i get an error 'undefined function 'UserID' in expression' (i have taken user id out and get the same error on Cont name)

I assume this is because either the query can't see the variables as they are within the form (however all public functions and form still open???) or have i just set up the update query wrong?"

You've hit the nail on the head here, your variables and functions need to be in a separate module which will make hem global to the entire project, not just the form in question. Simply copy my code from section 3 and paste into a new module.

Any more problems, let me know.




Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[cjjc]

Thanks again! i am getting there. Ive set up the separate module so ive got pass the undefined function problem.

re passing the values to the public variables - confusion! do i do this within the form's module? the query runs and states that it is updating the field so thats all ok, but im still getting blank fields. is it because the variables are being selected from a combo box which is using a lookup table? i need to use the lookup as the values could change in future. I have set a command button for the user to click once the values have been selected in the combo boxes, and have used this code as suggested for the click event.

Public Sub Command9_Click()

strContname = Me.cboContractor
strUserID = Me.cboUser

DoCmd.OpenQuery "Query1"

End Sub

yours frustratedly!
c

 

[SQLBI]

Hi,

Your code looks ok, but you may need to update the form after running the query.

Add

Form_frmYourForm.Requery

and that should update the fields within it.

Let me know how you get on.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk