Command Button

[Vwilson42]

A couple of weeks ago I asked the question of how to send a single record from one table to another (Archive table) using a command button. I got a response. I have had time to work with this a little. My problem is I keep getting a error message "Run-time error '3075'; Syntax error (missing operator) in Query expression "[New ID]='. I wanted to mention that the archive table has an "archive ID" field as well as "New ID" that links the tables. I have the following code. Please help with this if possible.

" Private Sub Command667_Click()
' Save the main record if it has not been saved.
If Me.Dirty Then
DoCmd.RunCommand acCmdSave
Me.Recalc
End If

If MsgBox("Do you want to archive this record?", vbYesNo) = vbYes Then
Dim SQL As String

' Move main record to Grants Activity Archive.
SQL = "Insert into [Grants Activity Archive]" & _
" select * from [Grants] where [New ID]=" '" & Me.ID & "'"
CurrentDb.Execute SQL
Me.Recalc
End If

End Sub "

The error seems to refer to the & Me.ID section. I have tried putting Me.New ID and also Me.Archive ID. The Grants Activity Archives is the subform where I want the record to go. This subform is set up like a form with many fields so it is a lot of work to type the main record from the Grants table to the Grants Activity Archive table every time you want to archive a record. I hope that makes sense.

 

[Bat17]

SQL is not my best field but I think you need to wrap the sub query in brackets, Also [New ID] is wrapped in quotes which is fine if it is a text field but not needed if numeric. I find the easies way to debug SQL is to add a Debug.Print statement, this lets you copy the generated SQL from the Imediate window (Ctr-G) and paste it into the SQL View of a query so you can see what is happening.
' Move main record to Grants Activity Archive.
SQL = "Insert into [Grants Activity Archive]" & _
"( select * from [Grants] where [New ID]=" '" & Me.ID & "')"
debug.print SQL
CurrentDb.Execute SQL
Me.Recalc


HTH

Peter

 

[Vwilson42]

Thanks, now for some reason it does not give me any errors but the record does not go to grants archive table. I don't know where it is going. I did not create this database and I can say a lot of things about it but I probably won't be getting that raise. I think now that because the grants archive table has an autonumber field to give each new archive an unique number(why I don't know) this is why I am have problems sending main record to archive table. Each record has many fields (about 60) This was turned into a form. On each form there is a subform with the same format as the main form with the exception that each of these subforms are considered our archive records. Each time the grant is amended. You have to manually type all 60 fields into subform which may be 1 main record with 3 subforms. I hope you are following. I wanted to hit a command button to transfer all info in main record to a subform connected to this main record.

Example:

Id field
Department
New Id (autonumber)
F1
F2, etc. up to about 60 fields

Subform
New ID (not auto)
Department
Archive ID (auto number)
F1
F2, etc. up to about 60 fields

I may have completely lost you. Gave you a whole project. But any advise will be greatly appreciated and welcomed!

 

[Mile-O]

F1
F2, etc. up to about 60 fields

I may have completely lost you.

You've lost yourself. You need to normalise your database before you go any further. Any series you get is an indication that you need a new table in your table.


Useful Links:

• Understanding Relational Database Design
• Microsoft Access Solutions

 

[Vwilson42]

Yeah I was thinking that. I think it goes in many directions with a lot of redundant things. I have been working with database for 10 years, so I am not that much of a novice but this one has me wondering.