updating a record from another table.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that I enter info into that could tie into a record on a nother
table. I would like to be able to pull the info from the other table and
insert it into the record I and inputting so I do not have to double enter
the record. The issue is that there may not always be a record that matches,
otherwise I would just create a relationship between the 2 tables and ber
done with it. My question is how do I get the info from the one table to the
other. It would search off a uniques number field and if it matches it would
pull 2 other number fields and a dollar amount. THis would happen about 90%
of the time, so it could be a big time saver for me. I was thinking an
update query, but I don't know if it would work as the record has not been
saved yet.

Hope this makes sense.
 
For a normalized database, one usually doesn't want to make multiple copies
of data. However, there are legitimate uses for such a feature as you
describe, but I'd like you to think first on whether this duplication of data
in two separate tables is in your best interest.

If you think it is in your best interest, then it's pretty easy to do this
by just clicking on a button that will find the record if it exists and
populate the appropriate fields on the form, or do nothing if no record
exists. For example, set a Refernce to the DAO library first, then try:

Private Sub UpdFldsBtn_Click()

On Error GoTo ErrHandler

Dim recSet As DAO.Recordset
Dim sqlStmt As String
Dim fOpenedRecSet As Boolean

sqlStmt = "SELECT OrderID, ProdID, UnitPrice " & _
"FROM tblOrderItems " & _
"WHERE (OIID = " & Me!txtOrdItemID.Value & ");"

Set recSet = CurrentDb().OpenRecordset(sqlStmt)
fOpenedRecSet = True

If (Not (recSet.BOF And recSet.EOF)) Then
Me!txtOrderID.Value = recSet.Fields(0).Value
Me!txtProdID.Value = recSet.Fields(1).Value
Me!txtPrice.Value = recSet.Fields(2).Value
End If

CleanUp:

If (fOpenedRecSet) Then
Set recSet = Nothing
fOpenedRecSet = False
End If

Exit Sub

ErrHandler:

MsgBox "Error in UpdFldsBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear
GoTo CleanUp

End Sub ' UpdFldsBtn_Click( )

.. . . where UpdFldsBtn is the name of the button, txtOrderID is the name of
the text box with the first numerical value to be copied to, txtProdID is the
name of the text box with the second numerical value to be copied to,
txtPrice is the name of the text box with the currency value to be copied to,
and txtOrdItemID is the name of the text box displaying the criteria to find
the appropriate record in the other table, tblOrderItems.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
When I originally set up the database it didn't have as much to it as it does
now. I am in the midst of normalizing it, but it is slow going.

Thanks, this should work great.
 
OK, I set up my code as seen below. When I fill in the Line Number and go
to my next field I get a runtime error 3061 Too few parameters. Expected 2.

Here is my code. I set it up to run after the update of the Line number
field.

Private Sub Line_Number_AfterUpdate()
'On Error GoTo Line_Number_AfterUpdate_Err

Dim recSet As DAO.Recordset
Dim sqlStmt As String
Dim FOpenedRecSet As Boolean

sqlStmt = "SELECT REF_, Amount FROM CurrentTB WHERE (Line_ =" & Me.[Line
Number] & ");"

Set recSet = CurrentDb().OpenRecordset(sqlStmt)
FOpenedRecSet = True

If (Not (recSet.BOF And recSet.EOF)) Then
Me![Claim Number].Value = recSet.Fields(0).Value
Me!Amount.Value = recSet.Fields(1).Value

End If

Line_Number_AfterUpdate_Exit:
If (FOpenedRecSet) Then
Set recSet = Nothing
FOpenedRecSet = False
End If

Exit Sub

Line_Number_AfterUpdate_Err:
MsgBox Err.Description
Resume Line_Number_AfterUpdate_Exit

End Sub
 
Back
Top