Insert statement using info from a query

[Rhys Davies]

Hi - i use query by forms a lot. In one instance when i open up a form that
has multiple record lines on it i would like to be able to click on a command
button that would then insert certain information such as userid, firstname,
surnaemet etc into a table, and i need it to do this for each line on the
form i.e. 100 or so. i can do it for a single record in other parts of my
database but i can never quite remember how to loop through and do it for
multiple records. Id imagine i have to open up a recordset and loop through
until i reach the end of file?
here is my code for a single record when the user logs into the database:

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "INSERT INTO tbluserslogin([userID],[loggedindatetime],[user])" & _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Any help would be great.

Thanks,

Rhys.

 

[John W. Vinson]

Hi - i use query by forms a lot. In one instance when i open up a form that
has multiple record lines on it i would like to be able to click on a command
button that would then insert certain information such as userid, firstname,
surnaemet etc into a table, and i need it to do this for each line on the
form i.e. 100 or so. i can do it for a single record in other parts of my
database but i can never quite remember how to loop through and do it for
multiple records. Id imagine i have to open up a recordset and loop through
until i reach the end of file?
here is my code for a single record when the user logs into the database:

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "INSERT INTO tbluserslogin([userID],[loggedindatetime],[user])" & _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Any help would be great.

Thanks,

Rhys.

It sounds like you want to *UPDATE* fields in existing records. INSERT INTO
does not do so; it creates new records. I'd change your query to something
like

UPDATE tbluserslogin SET UserID = ...

using appropriate criteria to identify which records you want to update (the
same criteria that select the records shown on the form).

The need to do this makes me really suspicious of your database design,
though! How are the records getting into the table in the first place? Might
it not be better to simply update the fields in the Form's BeforeUpdate event,
one record at a time, as the records are entered? Also, isn't storing the
UserID and the user loginname redundant?

 

[Rhys Davies]

Hi John, thanks for the reply, im probably not making myself very clear. The
insert into code example there just runs when a user logs into the database.
This is just so that i can see who is currently logged in. I thought it
might make a starting point for my new code.
Basically what i want to do is insert certain values from the query by form
into a separate table. the query will fom the basis of a mail merge to our
clients. I want to insert certain values from the form into a separate table
, call it tblmailinghistory for example and tie them back to the relevant
contact via userid so that i can have a subform on the contact screen that
will show me which mailings the contacts have had. So i would take userID,
date(), and an unbound field on the form where i enter a mailing name e.g.
March mailout and insert them into tblmailinghistory. insert into would be
fine because each new mailout would have different values such as date() and
mailing name, the only constant being the userID

Hi - i use query by forms a lot. In one instance when i open up a form that
has multiple record lines on it i would like to be able to click on a command
button that would then insert certain information such as userid, firstname,
surnaemet etc into a table, and i need it to do this for each line on the
form i.e. 100 or so. i can do it for a single record in other parts of my
database but i can never quite remember how to loop through and do it for
multiple records. Id imagine i have to open up a recordset and loop through
until i reach the end of file?
here is my code for a single record when the user logs into the database:

Dim strSQL As String
DoCmd.SetWarnings False
strSQL = "INSERT INTO tbluserslogin([userID],[loggedindatetime],[user])" & _
"SELECT" & "'" & useridno & "'," & _
"'" & Now & "', " & _
"'" & loginname & "'"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Any help would be great.

Thanks,

Rhys.

It sounds like you want to *UPDATE* fields in existing records. INSERT INTO
does not do so; it creates new records. I'd change your query to something
like

UPDATE tbluserslogin SET UserID = ...

using appropriate criteria to identify which records you want to update (the
same criteria that select the records shown on the form).

The need to do this makes me really suspicious of your database design,
though! How are the records getting into the table in the first place? Might
it not be better to simply update the fields in the Form's BeforeUpdate event,
one record at a time, as the records are entered? Also, isn't storing the
UserID and the user loginname redundant?

 

[John W. Vinson]

Hi John, thanks for the reply

Sorry I dropped the ball here... I'm leaving momentarily for a week! Please
repost as a new thread, one of the other volunteers should be able to help.