one form in two database

[problematic:)]

hi! can someone help me with my problem, I'm using msaccess2003 and vb6. I've been trying to determine how can I save data using one form to two database. the scenario is like this, I have one form, the details of some information of the customer will be input in the info table while the his payment will be saved in the bill table.

Here is my proposed code for saving

Dim RS As Recordset
Set RS = New Recordset
Set RS = cn.Execute("select * from weddingrecords leftjoin payment on weddingrecords.NameofBride = payment.Bride where TransactionNumber '" & tn.Text & "'")
If RS.EOF Then
    MsgBox "Transaction Not Found", vbExclamation
End If

I will also take the opportunity to ask for the search, delete and update buttons :)

I hope someone can help me. Thanks in advance!

[AndreRet]

Update will be something like -

RS.AddNew
RS!MyFieldNameHere = Text1.Text
'and so on....
RS.Update

Delete something like this -

RS.Delete 'Use bookmark etc for a specific record etc...

Search -

RS.Open "SELECT * FROM YourTable WHERE TheFieldName LIKE '" & Text1.Text & "'", cn, adOpenStatic, adLockOptimistic

[problematic:)]

Is this code would update in the two database?

[AndreRet]

Your execute statement looks fine so, yes, it should update both. If not, let me know as well as where the error occured or if the fields in payment table is empty.

[problematic:)]

well there's a debug on it. it says : syntax error in FROM clause
then it highlights this:

Set RS = cn.Execute("select * from weddingrecords leftjoin payment on weddingrecords.NameofBride = payment.Bride where TransactionNumber '" & tn.Text & "'")

[AndreRet]

Mmmm, I normally don't use join statements. I like to do the longer route, but I never get errors.

This is what I would've done -

Dim RS As ADODB.Recordset
Dim rsPayment As ADODB.Recordset

Set RS = New ADODB.Recordset
Set rsPayment = New ADODB.Recordset

RS.Open "SELECT * FROM weddingrecords WHERE TransactionNumber = " & "'" & tn.Text & "'", cn, adOpenStatic, adLockOptimistic

rsPayment.Open "SELECT * FROM payment WHERE NameofBride = "'" & txtBrideName.Text & "'", cn, adOpenStatic, adLockOptimistic

If RS.EOF Then
    MsgBox "Transaction Not Found", vbExclamation
      Else
   rs.AddNew
   RS!TransactionNumber = txtTransactionNumber
   RS.Update
   rsPayment.AddNew
   rsPayment!Amount = txtAmount.Text
   rsPayment.Update
End If

Will this help?:)

[problematic:)]

well some of it has errors but maybe I'll go try finding them myself. thanks a lot. you're making my name sounds like I don't :)

[AndreRet]

Fantastic! Happy Coding!:)

[Mark Alonzo16]

hello,

[Mark Alonzo16]

use two connection strings when you want to use two databases..then call the connection string that you will going to use.