473,382 Members | 1,433 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,382 software developers and data experts.

How to update multiple records in MS Access database from a list in a form

Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound) that displays the new Top 30 positions with additional information defined in the form such as Date Entered chart, Highest Position, etc. I have a command button (Update) that will launch an event to update each table entry for each record shown on the form. This event needs to loop through the records displayed in the form and then find the corresponding record in the database table and update certain fields. The following will successfully go through each record on the form but I am stuck with the coding to find the original record in the database (via its unique Record Number ID). FindFirst or a SQL statement gives a runtime error that states 'syntax error'.
What am I missing?
NB: I've commented out some code until I can confirm the search process works.

Private Sub Update_New_Info_Click()
'Update all entries then exit.

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

Dim W4SEARCH As String
Dim W4SQLCMD As String
Dim W4SQL As String

'Get the database and recordset
Set dbs = CurrentDb
Set rst = CurrentDb.OpenRecordset("TSSINGLE-WEEKLY")
'Set rstForm = Forms!Form_WCE140_FRM.Form.Recordset
Set rstForm = Forms!WCE140_FRM.Form.Recordset

'Define SQL statement
'W4SQLCMD = "UPDATE TSSINGLE-WEEKLY " & _
'(cntd)

W4SQLCMD = "UPDATE rst SET " & _
"rst![SWHIGHPOSN] = Forms!WCE140_FRM.Form!F1HIGHPOSN, " & _
"rst![SWWKSTOP30] = Forms!WCE140_FRM.Form!F1WKSTOP30, " & _
"rst![SWWKSTOP75] = Forms!WCE140_FRM.Form!F1WKSTOP75, " & _
"rst![SWFIRST30] = Forms!WCE140_FRM.Form!F1FIRST30, " & _
"rst![SWHIGHYEAR] = Forms!WCE140_FRM.Form!F1HIGHYEAR, " & _
"rst![SWWK30YEAR] = Forms!WCE140_FRM.Form!F1WK30YEAR, " & _
"rst![SWLEFT30] = Forms!WCE140_FRM.Form!F1LEFT30, " & _
"rst![SWLEFT75] = Forms!WCE140_FRM.Form!F1LEFT75, " & _
"rst![SWYEAR] = Forms!WCE140_FRM.Form!F1YEAR " & _
"WHERE rst![SWRECID] = Forms!WCE140_FRM.Form!SWRECID"

'"IIf (Forms!WCE140_FRM.Form!F1LEFT75' <> Null And rst![SWYEAR] = Null, " & _
'"rst![SWYEAR] = Year(Forms!WCE140_FRM.Form!F1LEFT75), rst![SWYEAR]) " & _


'Process all records
DoCmd.GoToRecord , , acFirst
rstForm.MoveFirst

Do While Not rstForm.EOF

'Find record via RRN
W4SEARCH = "[SWRECID] = Forms!WCE140_FRM.Form!SWRECID"

' Set rst = CurrentDb.OpenRecordset("TSSINGLE-WEEKLY")
' Set dbs = CurrentDb

W4SQL = ""
W4SQL = W4SQL & "SELECT * FROM TSSINGLE-WEEKLY "
W4SQL = W4SQL & "WHERE TSSINGLE-WEEKLY.SWRECID = " & Forms!WCE140_FRM.Form!SWRECID

' MsgBox W4SQL
' CurrentDb.Execute W4SQL
' DoCmd.RunSQL W4SQL
' rst.FindFirst rst![SWRECID] = Forms!WCE140_FRM.Form!SWRECID

' DoCmd.FindRecord W4SEARCH

' rst![SWHIGHPOSN] = Forms!WCE140_FRM.Form!F1HIGHPOSN
' rst![SWWKSTOP30] = Forms!WCE140_FRM.Form!F1WKSTOP30
' rst![SWWKSTOP75] = Forms!WCE140_FRM.Form!F1WKSTOP75
' rst![SWFIRST30] = Forms!WCE140_FRM.Form!F1FIRST30
' rst![SWHIGHYEAR] = Forms!WCE140_FRM.Form!F1HIGHYEAR
' rst![SWWK30YEAR] = Forms!WCE140_FRM.Form!F1WK30YEAR
' rst![SWLEFT30] = Forms!WCE140_FRM.Form!F1LEFT30
' rst![SWLEFT75] = Forms!WCE140_FRM.Form!F1LEFT75
' If Forms!WCE140_FRM.Form!F1LEFT75 <> Null And rst![SWYEAR] = Null Then
' rst![SWYEAR] = Year(Forms!WCE140_FRM.Form!F1LEFT75)
' End If
' rst.Update

' Set rstForm = Forms!WCE140_FRM.Form.Recordset
' DoCmd.GoToRecord , , acNext

' DEBUG - Show record ID for Form and for Table
MsgBox "Form RRN is " & Forms!WCE140_FRM.Form!SWRECID & "File RRN is " & rst![SWRECID]
rstForm.MoveNext
' MsgBox "Record ID is " & Forms!WCE140_FRM.Form!SWRECID
Loop

'MsgBox "SQL is " & W4SQLCMD
'DoCmd.RunSQL W4SQLCMD

'Exit
DoCmd.Close

End Sub
Mar 10 '24 #1

✓ answered by cactusdata

Why don't you bind the form to the table?
That will leave you with zero code.

1 10503
cactusdata
214 Expert 128KB
Why don't you bind the form to the table?
That will leave you with zero code.
Mar 13 '24 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: Mark S. | last post by:
Hi, I have a weird UPDATE sequence I want to perform. The customer does not want to use INSERT and DELETE. My issue is that there are multiple Mat_Class, but I do not wish to hard code each one...
0
by: jack daniels via .NET 247 | last post by:
Hi! Perhaps you know of a better way here. I have an Access database, actually a single table. Now, at the click of a button on a C# Form, each and every single cell associated with the primary...
1
by: A P | last post by:
How can I go about updating multiple records or deleting multiple records from a DB at a time?
1
by: Kenneth | last post by:
Dear all, How can I update multiple records in using ASP.NET? Currently I have a table which require to update frequently but I can update records one by one using DataGrid. Kenneth
1
by: soniasherry | last post by:
Can I please get some guidance.I'm new to MSACCESS 2003 How do I update all the tables in a main access database file with around 50 tables with unique records from similar tables placed in...
1
by: jllopez | last post by:
I need update multiple records in the datagrid but this is using ASP.net and VB, somebody that I can help?
2
by: susinthaa | last post by:
Hi, we are having some text values in the variable of the VB form and we have to insert these datas into the MS Access database. We have opened the database and only one record is inserted in...
1
Fary4u
by: Fary4u | last post by:
how can i update multiple records corresponding to perticular ID & update values in one go ? mode_a contains ID No. Dim mode,mode_a,i mode=Request("t1") mode_a=split(mode,",")
5
by: garymilam | last post by:
I have a date closed field on a form. How do I make this field update multiple records. For example. I have books database. I have multiple books checked out with a date. I want to update the...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.