Linking records between forms

[brendandaly]

I need to build a form that fills out a table with records that are linked to a master table but I can't figure it out. Some of the fields in the table need to be automatically filled out by information from the master table on a record by record basis. This seems pretty straight forward but I can't get this to work. Any help would be hugely appreciated.

 

[pbaldy]

You haven't given us much to go on, but is this applicable?

http://www.baldyweb.com/Autofill.htm

 

[brendandaly]

Yeah, I apologize, that wasn't the best description of the issue. That article is close to what I'm talking about. I've got this form that allows auditors to do edit records of doctor's office addresses that is based off of one table (lets say 'tblIndividuals'.) What I need to do is bring up another form (after a button click or a vbYesNo msgbox) that populates another table (tblMailing_Address) with a mailing address they would enter, and the name and ID number of the Dr from the first table. I've experimented with using a subform but the issue is that I need it to only open upon an action, not be open all the time. Thanks again!

 

[themurph2000]

Yeah, I apologize, that wasn't the best description of the issue. That article is close to what I'm talking about. I've got this form that allows auditors to do edit records of doctor's office addresses that is based off of one table (lets say 'tblIndividuals'.) What I need to do is bring up another form (after a button click or a vbYesNo msgbox) that populates another table (tblMailing_Address) with a mailing address they would enter, and the name and ID number of the Dr from the first table. I've experimented with using a subform but the issue is that I need it to only open upon an action, not be open all the time. Thanks again!

Well, you're halfway home. If you are able to use the dialog box to open up the second form (where they type in the address), you simply need to take the name of the text box in the first form and set the appropriate text box in the second form to match.

Following the opening of the form, you'd need lines of code similar to this.

dim docname as String
dim docID as Integer
 
docname = Forms!FirstForm.docnametextbox
docID = Forms!FirstForm.docIDtextbox
DoCmd.OpenForm "SecondForm", acNormal
Forms!SecondForm.docnametextbox= docname
Forms!SecondForm.docIDtextbox = docID

 

[pbaldy]

I've experimented with using a subform but the issue is that I need it to only open upon an action, not be open all the time.

If the subform works but for that, you can simply toggle its visibility and/or record source as appropriate to the situation.

 

[brendandaly]

Ok, I think this is going to work and I REALLY appreciate the help, but a couple of quick questions to get this going. I actually need the boxes in the second form (and thusly those fields on the second table) to be filled from the table the first form populates from because the form actually uses =[first]&""&[middle]&""&[last]& " " & [suffix]. This is what I currently have after trying to modify your code to the situation:

Private Sub Combo697_Click()

Dim docname As String
Dim docID As String

If STAT_TYPEADDR = "BUSN" Then
Answer = MsgBox("Does this address accept mail?", vbYesNo)
If Answer = vbNo Then
DoCmd.OpenForm "frmMailingAddress", acNormal, , , acFormAdd, acWindowNormal


docfirstname = Tables!tblMasterIndividual.FIRST
docmiddlename = Tables!tblMasterIndividual.MIDDLE
doclastname = Tables!tblMasterIndividual.LAST
docsuffixname = Tables!tblMasterIndividual.SUFFIX
docPIID = Forms!Individuals.HMS_PIID

DoCmd.OpenForm "frmMailingAddress", acNormal
Forms!frmMailingAddress.FIRST = docfirstname
Forms!frmMailingAddress.MIDDLE = docmiddlename
Forms!frmMailingAddress.LAST = doclastname
Forms!frmMailingAddress.SUFFIX = docsuffixname
Forms!frmMailingAddress.HMS_PIID = docPIID

End If
End If

End Sub

 

[brendandaly]

Btw, this code is returning a "Object Required" error.

 

[themurph2000]

Btw, this code is returning a "Object Required" error.

I may be saying this wrong (and someone please correct me if I am), but if you want to transfer data between one table and another, using the tables themselves is not the way to do it. Forms are designed as representatives for tables, so data entry and modifications should be done through forms rather than the tables themselves. (You can even create your form to look like a table.) I don't believe you can set VBA code to focus on a particular record on the table, but you can on the form, as the code uses the names of the text/combo boxes on the forms in its code.

If you have a copy of the database you can post, that may help people better illustrate it. But I'm somewhat certain what you're trying to do (assign the values via the tables) is not possible, hence the "Object Required" error.

 

[pbaldy]

Conceptually you can do what you're trying to do, but you can't reference the table that way. You would either need to use DLookup() or open a recordset to get a value from a table (a table unassociated with an open form of course). Given that you want to get several values, the recordset would be much more efficient. Here's an example from a old program I happen to have open:

  Dim strSQL                  As String
  Dim rslookup                As DAO.Recordset
  On Error GoTo ErrorHandler

  'Go look up new reservation system
  strSQL = "SELECT ResNum, AuthBy, PassName " & _
         " FROM tblReservations " & _
         " WHERE Resnum = " & Me.Reservation_num & ";"

  Set rslookup = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)

  If Not (rslookup.EOF) Then
    Me.Requestor = rslookup!AuthBy
    Me.Description = rslookup!PassName
  Else
    MsgBox "Reservation " & Me.Reservation_num & " Not found"
    Me.Reservation_num = vbNullString
    Me.Requestor = vbNullString
    Me.Reservation_num = vbNullString
  End If

ExitHandler:
  Set rslookup = Nothing
  Exit Sub

ErrorHandler:
  Select Case Err
    Case Else
      MsgBox Err.Description & " in Reservation_num_AfterUpdate "
      DoCmd.Hourglass False
      Resume ExitHandler
  End Select

 

[brendandaly]

No, no, no. The problem here was my ignorance. I didn't know that it was impossible to use VBA to focus on a specific record. I just added invisible text fields for FIRST, MIDDLE, LAST, SUFFIX and PIID, used your code (expanded for each name part) and bam. It worked like a charm. THANK YOU SO MUCH. I had tried to do something similar in vba earlier but botched it like a weasel cuz I was trying to get it to focus on the individual form. Seriously, Thanks a ton themurph2000 and pbaldy too. I appreciate your patience so so much. Next time I'm in Charlotte I'll buy you a drink.

 

[brendandaly]

Oh and pbaldy, you are officially the most helpful and generous objectivist I've ever met.

 

[pbaldy]

Happy to help! I don't fit well into any box, including that one. "Constitutionalist" is probably closest.

 

[themurph2000]

No, no, no. The problem here was my ignorance. I didn't know that it was impossible to use VBA to focus on a specific record. I just added invisible text fields for FIRST, MIDDLE, LAST, SUFFIX and PIID, used your code (expanded for each name part) and bam. It worked like a charm. THANK YOU SO MUCH. I had tried to do something similar in vba earlier but botched it like a weasel cuz I was trying to get it to focus on the individual form. Seriously, Thanks a ton themurph2000 and pbaldy too. I appreciate your patience so so much. Next time I'm in Charlotte I'll buy you a drink.

No prob. Glad I could earn my keep around here for once instead of mooching off of other folks. I'd probably have pbaldy on speed dial if I had everybody's number.