Add new record to outside Database

[wtrimble]

Hello,

I'm trying to add a new record to a different database than I'm in when a button is pushed. The following code is this:

Dim db As database
Dim rs As Recordset
 
 Set db = OpenDatabase("P:\BU Regeneration\XRF Laboratory\chemistry.mdb")
 Set rs = db.OpenRecordset("chemistry", dbOpenDynaset)
 
With rs
.AddNew
.Fields("XRF #") = "99999"
.Fields("Project #") = "9999"
.Update
.Close
End With
MsgBox "Done!"
Exit_this:
Set rs = Nothing
Set db = Nothing
Exit Sub

The first field in the updated table ("Chemistry") is an Autonumber field, which will populate the next correct number, and I want the next two fields ("Xrf #" & "Project #") to fill in as specified. However the program won't create a new record in the database. I put in ".Movefirst" and then msgboxed the contents to see if it was actually finding the recorset, and it was, so the pathway is correct. I must be missing something...

Any help is greatly appreciated.

 

[Trevor G]

Why not create an append query ? you can select to append to another Database and the table to append to.

 

[wtrimble]

How do you specify what field you want to insert if the field names are different?

 

[wtrimble]

Never mind I figured it out. Good idea. Thanks

 

[wtrimble]

Wait this doesn't work, I don't want a pop up to appear when the user pushes the button ("You are about to Update 1 field to...") so I need to do it the way I had before. Any thoughts on my first post???

 

[wtrimble]

No one has any thoughts on it??

 

[SOS]

How are you running the query? If you use

db.Execute "queryNameHere", dbFailOnError

you should be fine without messages.

 

[wtrimble]

No I'm not running the query. I need to go by the method I showed in the initial question

 

[SOS]

Then using

db.DoCmd.SetWarnings False

db.Docmd.SetWarnings True

might work for you.

 

[wtrimble]

Ah, I'll give that a try

 

[wtrimble]

Yes, that works. Thanks a lot.

 

[G37Sam]

There must've been a mismatch with fieldnames which is why it didn't work in the first place