I have an excel sheet with 4 pieces of data I would like to send to an access database. The fields in the database are:
quoteNumber (which is the primary key)
quoteDate
quoteCustomer
quoteProfile

I fill in the spreadsheet then press a command button with the following code:

Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase("C:\QuoteDB.mdb")
Set rs = db.OpenRecordset("quoteInfo", dbOpenTable)
rs.AddNew
rs.Fields("quoteNumber") = Me.txtQuoteNumber.Value
rs.Fields("quoteDate") = Me.cboDate.Value
rs.Fields("quoteCustomer") = Me.txtCustomer.Value
rs.Fields("quoteProfile") = Me.cboProfile.Value
rs.Update
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

This works fine if I'm adding a brand new record. How would I loop through to find and overwrite an existing record that matches me.txtQuoteNumber if I wanted to make a change.

Basically, I'm trying to use the spreadsheet everyone already uses to populate the database. Any help or other suggestions are appreciated.

I didn’t understand your question!
:confused:

My Questions?

1. Why reinventing wheel?

I don’t understand why you would want to append the data from Excel to access; you can create a MS access from that will do everything for you directly.

Access solution:

If you are using MS Access as front end you may want have a look at Allen’s work on http://allenbrowne.com/AppFindAsUType.html (http://allenbrowne.com/AppFindAsUType.html)

rs.FindFirst "quoteNumber = " & Me.txtQuoteNumber.Value

If rs.NoMatch Then
rs.addnew
.... and everything else
Else
rs.Edit
.... and everything else
End if
rs.Update

Is that what you're after?

Regards,
Pete

Thanks for the responses. That's exactly what I'm trying to do Pete, but when I put it in I get runtime error 3251 - "Operation not supported for this type of object."

I've tried it with rs.findnext, and rs.seek as well.

I get the error on this statement,
rs.FindFirst "quoteNumber = " & Me.txtQuoteNumber.Value

aahh ... change the dbOpenTable to dbOpenDynaset and it'll work fine.

Pete.

Now it's error 3070 - jet engine does not recognize 'QU0003' as a valid field name or expression.

Thx for the help. I think you've put me on the right track. I'll have to check back in the morning.

Thanks again Pete,
I just had to change the field to number instead of text and it seems to be working well.