Microsoft Access Tips
- You want one table to automatically pick up the ID value from another table as a foreign key.
Build a form with a subform. The main form is the record that has the primary key you want. Access will automatically add that as the foreign key to the second table represented by the subform.
- You want one table to automatically pick up the ID value from another table as a foreign key, but you don't want the table you are filling out in a subform because not every record will be linked with a record from the table with the primary key.
Example: you have a table that keeps a daily log of calls you make. Some calls are to Clients and some calls are to internal departments related to particular projects. The calls that are related to Clients should pick up the ClientID, but internal calls shouldn't.
Make the form for your CallLog table fields. In the form Design View, use the toolbar to add a ComboBox that picks up the ClientName and ClientID from the Clients table. In the ComboBox wizard, be sure to choose that ClientID from Clients will be stored in ClientID in the CallLog table.
- You want another field to pick up a value from a ComboBox.
Example: You want your DailyLog table to have a field for the name of the Client, but you don't want to type it in each time. So, it should pick up a value from the Clients table based on the ID picked up from the ComboBox.
Wrong: you can't set a default value here because the timing is wrong. A default value happens when that record loads in the form. Since it is based on something you are picking up from the ComboBox, it can't happen until after you make that choice. This has to happen after you update the ID.
Solution: the wizard allows the ComboBox to send a value to one field. To send a different value from the Combo Box to another field:
Go to Design View -> Right click the ComboBox and choose Properties -> Go to the Events tab and click the ... on AfterUpdate -> This will bring up Visual Basic. Inside the AfterUpdate function type: Me![FieldNameToBeChanged] = Me![NameOfComboBox].Column(NumberOfColumnToBePickedUp). "Me" tells Access to find the field in that same form.
- You want a field to pick up part of something you type in another text field.
Example: You have a FirstName field and you want another field to pick up the first initial.
Solution: First, the Initial textbox can't pick up anything until you type the name; so, this isn't a function of the Initial textbox, but a function of the FirstName textbox. It will be an AfterUpdate function. Go to Design View -> right click the FirstName textbox -> choose Properties -> go to the Events tab -> click the ... on AfterUpdate -> type Me![Initial] = left(Me![FirstName], 1).
- Changing the name of a form element has side effects.
- If you have Events attached to the element, they won't work any more because the VB is referencing a name that is no longer attached to that element.
|
|
