Update a table based on query results via VBA

[DonnaCulff]

Good afternoon all,

I have a query which picks up completed work requests from a work request table (which is linked to Sharepoint)

I would like to add some of the information from that query to a previously created work request table where work requests were entered manually (I want to now update it via the query)

But I cannot get it to update all the results, just the top line.
Here is my code:

CurrentDb.Execute "INSERT INTO tblWorkRequests(WorkRequestID,AnalystName,DateOfRequest,Requestor,WorkRequestTitle,WorkRequestDescription,RequestCompletionDate)VALUES ('" & Me.txtID.Value & "', '" & Me.Assigned_To.Value & "', '" & Me.Created.Value & "', '" & Me.Work_Request_Creted_by.Value & "', '" & Me.Title.Value & "', '" & Me.Description.Value & "', '" & Me.Modified.Value & "')"

This works and doesnt bring back any error messages on a button click which is on a form showing the query results

But, It only adds the one record every time.
If I place my cursor onto the second record on the form, and press the button, it will then, and only then add the next record.

I have tried lots of loops but have myself gone round in circles getting no where. Can anyone help?

 

[WayneRyan]

Donna,

You need to make a query to extract the values for txtID, Assigned_To, Created, Work_Request_Creted_by, Title,
Description and Modified

Then:

CurrentDb.Execute "INSERT INTO tblWorkRequests (WorkRequestID, AnalystName, DateOfRequest, Requestor, WorkRequestTitle, " & _
                  "                             WorkRequestDescription, RequestCompletionDate) " & _
                  "Select txtID, Assigned_To, Created, Work_Request_Creted_by, Title, " & _
                  "       Description and Modified " & _
                  "From   YourNewQuery"

hth,
Wayne

 

[c_smithwick]

Your current query is only adding one record because it is only running for the currently selected record on the form. You need to establish reference to the forms recordset in vba and then execute a do loop to run the recordset and execute your query for each record. Why don't you just set your forms data source to a query against the records of choice in the underlying table, then as you fill in the blanks on the form, your records in the table will be updated.

 

[WayneRyan]

Donna,

Is your goal to process all of the records on your form?

If it is, you can just use the syntax in post #2 of this thread and
change the "YourNewQuery" to the RecordSource of your form.

If it isn't, you'll have to either define a way to select them via
SQL (I can't help with that) or settle for doing them individually.

Wayne

 

[DonnaCulff]

Firstly thank you Wayne and c smithwick for your speedy replys

The following in post 2 is correct:
"Your current query is only adding one record because it is only running for the currently selected record on the form."

My code (Post 1) does work for the selected record on the form.

I tried to create a recordset using SQL but it didn't like the syntax of it and I am not sure about the rest of the loop itself... (Code below)

Option Compare Database

Private Sub cmdExitandUpdate_Click()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("SELECT [Work Requests].ID, [Work Requests].[Assigned To], [Work Requests].Created, [Work Requests].[Work Request Creted by], [Work Requests].Title, [Work Requests].Description,[Work Requests].Modified, & _
FROM [Work Requests] WHERE ((([Work Requests].Status)="Completed"));")

Do While Not rst.EOF
For nIndex = 0 To (rst.Fields.Count - 2)
CurrentDb.Execute "INSERT INTO tblWorkRequests(WorkRequestID,AnalystName,DateOfRequest,Requestor,WorkRequestTitle,WorkRequestDescription,RequestCompletionDate)VALUES ('" & Me.txtID.Value & "', '" & Me.Assigned_To.Value & "', '" & Me.Created.Value & "', '" & Me.Work_Request_Creted_by.Value & "', '" & Me.Title.Value & "', '" & Me.Description.Value & "', '" & Me.Modified.Value & "')"
Next
rst.MoveNext
Loop
Set rst = Nothing
MsgBox "Added"
End Sub

Wayne,

I gave your code a try and got a message saying there were too few parameters. I have a play around with it but couldn't get it to work. (There is an issue with the fact that the table that imports the information from sharepoint has gaps and characters which Access doesn't like so I am in the process of telling the person who created it to change it... but it was working with _'s between the gaps, and in my loop I used the square brackets ...

In reply to "Is your goal to process all of the records on your form?"
I want some of the records (new ones that have just appeared) on the form to pass to the tblWorkRequests table (tblWorkRequests has a work request ID field which is set to a primary key so if it has been added before It wont be added again, which is what I want)

Hope this isn't getting too confusing? Let me know if there is anything else I can post.

I will keep trying with both your ways, thanks again for the advice

 

[DonnaCulff]

I have tried doing something a little different but again, my loops wrong as only the one selected on the form is added...

Private Sub cmdExitandUpdate_Click()

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("select * from tblWorkRequests")
Do While Not rst.EOF
For nIndex = 0 To (rst.Fields.Count - 2)

rst.AddNew
'--- transfer data from text boxes to table fields
rst!WorkRequestID = txtID
rst!WorkRequestTitle = Title
rst!Requestor = Work_Request_Creted_by
rst!AnalystName = Assigned_To
rst.Update 'save
Next
rst.MoveNext
Loop

rst.Close 'close recordset
Set rst = Nothing 'reclaim memory recordset was using
MsgBox "Added"

End Sub

 

[c_smithwick]

Try the following:

Private Sub cmdExitandUpdate_Click()
 
Dim rst As Recordset
Dim nIndex As Integer
Dim rstForm As Recordset
Set rst = CurrentDb.OpenRecordset("select * from tblWorkRequests")
Set rstForm = Forms("[Insert Your FormNameHere]").RecordsetClone
rstForm.MoveFirst
Do While Not rstForm.EOF
     rst.AddNew
     rst!WorkRequestTitle = rstForm!Title
     rst!Requestor = rstForm!Work_Request_Creted_by
     rst!AnalystName = rstForm!Assigned_To
     rst.Update
     rstForm.MoveNext
Loop
rst.Close
rstForm.Close
Set rst = Nothing
Set rstForm = Nothing
 
End Sub

 

[DonnaCulff]

Hi, thanks for the code.
I have just given it a go and It only updates the selected record again.

Were you meant to declare nIndex?. Its not used in the code. Should I have added something?



I will check my form name and treak brackets see if I can get it working.
Cheers

D

 

[DonnaCulff]

OK, things have changed a little on this project so I have only just been given the new spec. Basically though I need to do the same. I need to send data bound to a form to a seperate table.

Thanks to C smithwick I have made a start:

This is what I have so far:

Private Sub cmdExitandUpdate_Click()

Dim rst As Recordset
Dim nIndex As Integer
Dim rstForm As Recordset
Set rst = CurrentDb.OpenRecordset("tblWorkRequestsExcel") 'the table i want to write to
Set rstForm = Forms("[frmCompletedWorkRequestsFROMteamsite]").RecordsetClone
rstForm.MoveFirst
Do While Not rstForm.EOF

rst.AddNew

rst!WR = rstForm!WR
rst!Originator = rstForm!Originator
rst![Assigned To] = rstForm!assignedto '<<Errors here saying Item not found in collection
rst!Summary = rstForm!Summary
rst!Description = rstForm!txtDescription
rst![Origin Date] = rstForm!OriginDate
rst![Desired Completeion Date] = rstForm!DesiredCompleteionDate
rst![Comp# Date] = rstForm!CompDate

rst.Update
rstForm.MoveNext

Loop
rst.Close
rstForm.Close
Set rst = Nothing
Set rstForm = Nothing

End Sub

I have checked the spelling of every field and copy and pasted it across. Its the 1st field that has spaces though so I am thinking that perhaps the square brackets (which i thought were required) is what was causing the problem

I will try and take the spaces out (although this will cause problems elsewhere) on one field to see if thats the case. Other than that, is the above code correct for what I am trying to achieve?

 

[DonnaCulff]

I Have removed the spaces and still no louck. Does my code look OK to you guys?

 

[DonnaCulff]

Perhaps I should learn to read. After entering 'select *' and renaming fields, it kind of works. I know it kind of works as it is running until the update line of code. I then get an error message regarding duplicate values. So my new challange is to amend the code to only enter records that are new.

Heres the code:

Private Sub cmdExitandUpdate_Click()

Dim rst As Recordset
Dim nIndex As Integer
Dim rstForm As Recordset
Set rst = CurrentDb.OpenRecordset("select * from tblWorkRequestsExcel")
Set rstForm = Forms("[frmCompletedWorkRequestsFROMteamsite]").RecordsetClone
rstForm.MoveFirst
Do While Not rstForm.EOF

rst.AddNew

rst!WR = rstForm!WR
rst!Originator = rstForm!Originator
rst!assignedto = rstForm!assignedto
rst!Summary = rstForm!Summary
rst!Description = rstForm!Description
rst!OriginDate = rstForm!OriginDate
rst!DesiredCompleteionDate = rstForm!DesiredCompleteionDate
rst!CompDate = rstForm!CompDate

rst.Update
rstForm.MoveNext

Loop
rst.Close
rstForm.Close
Set rst = Nothing
Set rstForm = Nothing

End Sub