Look and Stuff in Microsoft Access


I think any database analyst or programmer has heard this common client request when doing mailing lists:

I want to be able to look up the organization data if the person is a member of an organization, and copy all of the relevant information into my mailing list. But I also want to be able to type in (or over) any existing organization information. And if the person isn’t a member of an organization, or if the organization doesn’t exist in the table, then I want to be able to add it right then…

So we stand in really odd positions to make this work, trying to relate the person table to an organization table and put together some kind of logic that works like QuickBooks (which no doubt cost $2.45 million to develop…)

This recently came up again, and because it turns out that the Organzation table gets replaced periodically, I gave up on the idea of relating the two tables, and just decided to duplicate the organization’s fields in the person table. So shoot me.

If you press the “Look and Stuff” button, it brings up a subform which contains a single control, a combo-box pick list which picks an organization from the organization table.

The code behind this takes the field contents for the chosen Organization record and copies them to equivalent fields in the person table. The code:


Private Sub cmdFill_Click()
On Error GoTo Err_cmdFill_Click
Dim dbs As DAO.Database
Dim strSQL As String
Dim rstTemp As Recordset
Set dbs = CurrentDb

'Only do the following if the user has chosen a company
If Me.cboCompany.Value > 0 Then
strSQL = _
"Select * FROM Company Where Company_id =" & Me.cboCompany.Value
Set rstTemp = _
CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

'Stuff the results into the Mailing List Form
Form_Maillist.txtOrganization = rstTemp![Organization]
Form_Maillist.txtAddress = rstTemp![Address]
Form_Maillist.txtCity = rstTemp![City]
Form_Maillist.txtZip = rstTemp![Zip]
Form_Maillist.txtState = rstTemp![State]

End If
'Close the chooser form
DoCmd.Close
Exit_cmdFill_Click:
Exit Sub
Err_cmdFill_Click:
MsgBox Err.Description
Resume Exit_cmdFill_Click
End Sub
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s