Basing a form recorset on a user choice from previous form

[cluksha]

My apologies for the dual post. I found the VBA forum only after I posted in the Acces forum. I think it is ore appropriate here...

I'm attempting to create a form that will ask the user which extate they want (via combo box, so the msgbox is out of the picture), and from that choice, open the new form that gives all the records related to the user choice, and then close the form that asked which estate.

If that made sense, I need some help.

Thanks!
Chris


Chris Luksha
Echo Web Services
Making Your Website Resound

http://www.echowebservices.com

 

[kjv1611]

Have you tried using SQL (build a query, and copy/paste the code from the query to the afterupdate event of your combo box) or the Dlookup function? If don't know about Dlookup, then pull it up in the VBA help file. You can also find many examples here on tek-tips.

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[kjv1611]

If you are just wanting to pull up one record, I believe you can use a DAO.Recordset in conjunction with the DLookup function... Something like this, maybe:

cmbChooseEstate_AfterUpdate()

  Dim db as DAO.Database
  Dim rs as DAO.Recordset
  Set db = CurrentDb
  Set rs = db.OpenRecordSet("tblEstatesTable")

    
  If DLookup("[Estate Name]", "tblEstatesTable", & _
      "[Estate Name] = " & cmbChooseEstate) Then
      DoCmd.OpenForm("frmNewForm")
      Forms!frmNewForm!txtEstateName = rs.Fields("Estate Name")
      Forms!frmNewForm!txtEstateAddress = rs.Fields("Address")
      Forms!frmNewForm!txtContactName = rs.Fields("Contact")
      Forms!frmNewForm!txtInsurance = rs.Fields("Insurance")
        [blue]ETC, ETC, ETC
  End If
End Sub

Well, anyway, that is somewhat the idea, but I think some extra work will need to be added, b/c I don't think it will necessarily pull all the values from the specific recorset item# you are looking for, but can't remember for sure, so maybe it will work like this.. Try it with a couple of the fields first, and see if it works.


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV