A problem with MS Access forms.

[rawhunger]

Hello all...

I have a bit of a problem here. You see, I am building a database in MySQL to replace the one we use in FileMaker and I am using MS Access as a user interface for updating the data. Anyway, we would like to get the same look and feel in the MS Access forms that we had in our FileMaker forms. Well, there is one thing that we would like, but I have no idea how to do it in Access: we have two forms, a "clients" form and an "events" form. The clients table and the event table have a one-to-many relationship by client Number. In the client form, if we put in a client Number that already exists in a new record for the events table, we would like client data (Organization, Address, City, etc...) to be populated in that form so we don't have to enter all that data again for an existing client. In FileMaker, it's easy to do this(so I'm told... I don't know for sure myself), but how would you do this in MS Access? If anyone has any hints or knows how to accomplish this, I would greatly appreciate the help. Thanks!

 

[rdodge]

In your Event Form, you can create a Table Type recordset of your Client Table. Me, I been using DAO programming, but then there's basically 4 different general ways of programming in Access (Access 97 does not have ADO, so only 3), which are ADO, DAO, Jet Engine (I.e. back end databases), and also via Linked Tables. Yes, you can have Tables and all other DB objects in the same DB file, but it's highly NOT recommended, but rather have only the data and table structures in the back end and everything else in the front end.

Now, here's my DAO example:
Dim dwsWS as DAO.Workspace, ddbDB as DAO.Database, drsClient as DAO.Recordset

Private Sub Form_Open(Cancel as Integer)
Set dwsWS = CreateWorkspace("Client", "Admin", "", dbUseJet)
Set ddbDB = OpenDatabase("\\FileServerName\SharedFolder\Client.mde", False, True)
Set drsClient = ddbDB.OpenRecordset("Client",dbOpenTable,dbSeeChanges,dbReadOnly)
drsClient.Index = "fldID"
End Sub
Private Sub Form_UnLoad(Cancel As Integer)
drsClient.Close
ddbDB.Close
dwsWS.Close
End Sub

This code above sets up the recordset to be used. The code of which other form could then have the Value Property of the Client ID Textbox to be the value of the Client ID number.

Set up the following sub for your form events to use:

Private Sub RefreshClientInfo()
drsClient.Seek "=", CStr(Me.tbxClientID.Value)
If drsClient.NoMatch = False Then
Me.lblName.Caption = drsClient.Fields("fldName&quot.Value
Me.lblStreetAddress1.Caption = drsClient.Fields("fldAddress1&quot.Value
Me.lblStreetAddress2.Caption = drsClient.Fields("fldAddress2&quot.Value
Me.lblCity.Caption = drsClient.Fields("fldCity&quot.Value
Me.lblState.Caption = drsClient.Fields("fldState&quot.Value
Me.lblZip.Caption = drsClient.Fields("fldZip&quot.Value
End If
End Sub

Now with the above sub procedure setup, you can setup the form's activate event and the client's id afterupdate event to refresh the information.

Private Sub Form_Activate()
RefreshClientInfo
End Sub
Private Sub tbxClientID_AfterUpdate()
RefreshClientInfo
End Sub

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000