Using RecordSet to insert new data into a query based on two forms

[DRathbone]

This forum has been so useful to me so far... but having searched through a load of topics for a few hours now I just cannot find a correct method of having the ability to update two tables from a form.

I firstly created a query that selected the nessary fields I wish to update from the two tables.

And its apparent that I need to use RecordSet to insert the information from the form into the query.

I have found a few different ways of doing this - none of which work for me HELP!

Method 1

Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
    If messageusr = vbYes Then
    
       Set db = CurrentDb()
       Set rs = db.OpenRecordset("SELECT [Backup Request].Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")

        Me.txtserver = rs!Server
        Me.cmblocation = rs!Location
        Me.cmbtype = rs!BackupType
        Me.cmbtype = rs!Type
        Me.cmbsize = rs!Size(GB)
        Me.cmbassign = rs!Group
        Me.txtrequiredby = rs!Date_required_by
        Me.txtrequestedby = rs!Requested_by
        Me.txtrequest = rs!Date_Time_Request
        Me.txtnotes = rs!Notes     
    
    'Clear fields on form to indicate write has occurred
        txtserver = ""
        cmblocation = ""
        cmbtype = ""
        cmbsize = ""
        cmbassign = ""
        txtrequiredby = ""
        txtrequestedby = ""
        txtrequest = ""
        txtnotes = ""
        
    'Close recordset and database
   	rs.Close
        db.Close
        MsgBox "This information has been succesfully saved"
    End 'return user back to form
    End If
 
End Sub

Method 2

Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackupRequest
    
    Dim db As DAO.Database
    Dim sqlStatement As String
    Dim saverecord As DAO.Recordset
    
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
    If messageusr = vbYes Then
        
       sqlStatement = "SELECT [Backup Request].Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;"
     
       Set db = CurrentDb()
       Set saverecord = db.OpenRecordset(sqlStatement)
 
        
        saverecord.AddNew
        saverecord(0) = txtserver
        saverecord(1) = cmblocation
        saverecord(2) = cmbtype
        saverecord(3) = cmbtype
        saverecord(4) = cmbsize
        saverecord(5) = cmbassign
        saverecord(6) = txtrequiredby
        saverecord(7) = txtrequestedby
        saverecord(8) = txtrequest
        saverecord(9) = txtnotes
        saverecord.Update              'Write new record to database
    
    'Clear fields on form to indicate write has occurred
        txtserver = ""
        cmblocation = ""
        cmbtype = ""
        cmbsize = ""
        cmbassign = ""
        txtrequiredby = ""
        txtrequestedby = ""
        txtrequest = ""
        txtnotes = ""
        
    'Close recordset and database
        saverecord.Close
        db.Close
        MsgBox "This information has been succesfully saved"
    End 'return user back to form
    End If
 
End Sub

and I have even looked into an insert sql statement
Method 3

Dim SQL_Text As String
SQL_Text = "INSERT INTO Backup Request (Server, Location, Type , Size(GB)) VALUES ('#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#') &"
INSERT INTO Process General (Group, Date required by, Requested by, Date/Time of request, General_Type, Notes;"
Docmd.RunSQL (SQL_Text, false)

Method 1 seems to be popular but its returning the message

Run time error '3061'
Too few parameters. Expected 3.

 

[boblarson]

I can't stress enough not to use Access reserved words NOR SPECIAL CHARACTERS in field names. You have a field named "Size(GB)" and with the parenthesis it makes it look like you have a function named Size with a parameter. Also, you have a field named TYPE (reserved Access keyword), and a field named GROUP (another reserved keyword).

Now, that may not be the problem, but I'm guessing it is and I don't have to guess to know that those WILL cause you grief at some point, maybe not today, maybe not tomorrow, but it will. So, rename those fields.

 

[DRathbone]

Field names changed. Thanks for the advance.

My initial post still stands though I still get the same error

Run time error '3061'
Too few parameters. Expected 3.

 

[boblarson]

Which line is giving the error?

 

[DRathbone]

Sorry Bob the error has slightly changed now..

Run time error '3061'
Too few parameters. Expected 1.

on line....

Set rs = db.OpenRecordset("SELECT [Backup Request].Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].ProcessType, [Backup Request].Size, [Process General].Group, [Process General].Date_required_by, [Process General].Requested_by, [Process General].Date_Time_of_request, [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")

 

[DRathbone]

After reviewing the SQL statement I had declared incorrect field names based on my changes.

The function *seems* to run - I now get my msgbox telling me the info was added - YET when I look into the tables no data has been added

 

[boblarson]

Set a breakpoint at the start of your update and make sure that something is really getting from the control into the recordset. Maybe your SQL statement is creating a non-updateable query. I don't use DAO myself, I use ADO so I am not totally sure about it.

 

[RuralGuy]

DRathbone,
If you are using Method 1 from your first post, that code is *getting* data from your recordset and then clearing the controls where you put the data. Method 2 has a *much* better chance of doing what you want!

 

[DRathbone]

Ahh so method 1 is more of a retrieval ? where as method 2 would be better as a write/update method?

 

[RuralGuy]

You *could* use the code of Method 1 to write/update but you would have to reverse the assignments and add the .AddNew and .Update commands that are in Method 2.

 

[DRathbone]

Thanks for the advice, I amended the code as per below:

Private Sub Save_Record_Click()
'Save all entered information to tblprocess request and tblBackup Request
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information")
    If messageusr = vbYes Then
     
       Set db = CurrentDb()
       Set rs = db.OpenRecordset("SELECT [Backup Request].Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].ProcessType, [Backup Request].Size, [Process General].UsrGroup, [Process General].Date_required_by, [Process General].Requested_by, [Process General].Date_Time_of_request, [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")
        
        rs.AddNew
        rs!Server = Me.txtserver
        rs!Location = Me.cmblocation
        rs!BackupType = Me.cmbtype
        rs!ProcessType = "Backup Request"
        rs!Size = Me.cmbsize
        rs!UsrGroup = Me.cmbassign
        rs!Date_required_by = Me.txtrequiredby
        rs!Requested_by = Me.txtrequestedby
        rs!Date_Time_of_request = Me.txtrequest
        rs!Notes = Me.txtnotes
        
        rs.Update
        rs.Close
          
    'Clear fields on form to indicate write has occurred
        txtserver = ""
        cmblocation = ""
        cmbtype = ""
        cmbsize = ""
        cmbassign = ""
        txtrequiredby = ""
        txtrequestedby = ""
        txtrequest = Now()
        txtnotes = ""
        
        db.Close
        MsgBox "This information has been successfully saved"
    End 'return user back to form
    End If
 
End Sub

and its returning the below error:

Run time error 3201

You cannot add or change a record because a related record is required in table 'Process General'

highlighting....

rs.Update

There are fields within the 'Process General' table I have not selected within the SQL statement but I would have though it would just add the record and leave the none selected fields as null. I'm unsure what is causing this error now

 

[RuralGuy]

That is your ReferentialIntegrity kicking in. Have you satisfied any ForeignKeys in the tables?

 

[DRathbone]

in both tables the primary key is a field labeled 'Process ID' (autonumber)

 

[boblarson]

Does that mean that you have a field in the table "Backup Request" named Process ID with autonumber as it's datatype and you also have a field in the Process General table named Process ID with autonumber as it's datatype? And these are tied together in your relationships?

That would be your problem then. You need to connect the primary key of the one table (Process ID-autonumber) to the foreign key in the other table (Process ID - Long Integer).

 

[DRathbone]

That was indeed my problem, thank you all for your help