Excel as record source

[puffer317]

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.

 

[ritwik_shukla]

I didn’t understand your question!


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

 

[petehilljnr]

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

 

[puffer317]

Very close

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

 

[petehilljnr]

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

Pete.

 

[puffer317]

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

 

[puffer317]

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

 

[puffer317]

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