Automatically looking up/filling in Microsoft Access field values
Category: Computers > Software
Asked by: bruces-ga
List Price: $60.00
17 Mar 2003 14:54 PST
Expires: 16 Apr 2003 15:54 PDT
Question ID: 177507
My database is in Access 2000 format. Im familiar with VBA code, but as shown by my question below, I am far from being an expert. I have designed a table named tblReferrals. It contains fields named JNumber, FirstName, and LastName. There will be about 500 records in this table. It is possible that after a record is entered into the table, it will be necessary later to correct or change the JNumber, FirstName, and/or LastName entries. JNumber is the Primary Key in tblReferrals, so each value is unique. However, it is conceivable that two records will have an identical combination of FirstName and LastName entries. I have designed another table. Its named tblAif. It is linked to tblReferrals in a one (tblReferrals) to many (tblAif) relationship using JNumber as the linking field. The user enters data into tblAif using a form named frmAif. The Primary Key in tblAif is a field that is formed by a concatenation of two fields, JNumber and Milestone. Ive set up an error trapping routine such that if a user enters a JNumber on frmAif that does not have an existing JNumber match in tblReferrals, the user is instructed to close frmAif and arrange for the proper entry to be made in tblReferrals. What are the best ways for me to accomplish these further objectives? 1. Allow the user to enter the JNumber directly on frmAif by typing it into the JNumber text box. Upon completion of the entry of the JNumber, the FirstName and LastName boxes would be automatically filled in on frmAif, with the data items retrieved from tblReferrals, to help the user check that this is, in fact, the right JNumber. 2. If it is possible, I would also like to allow the user to enter the JNumber into tblAif indirectly by typing in FirstName and LastName entries on frmAif. This would automatically fill in the corresponding JNumber, retrieved from tblReferrals, and when the user exited the record in frmAif, the JNumber entry appearing on frmAif would be stored in the JNumber field in tblAif. If the user typed in a combination of FirstName and LastName that was not unique, the user would get a message with the theme, Youll need to enter the JNumber yourself on frmAif.
Re: Automatically looking up/filling in Microsoft Access field values
Answered By: hammer-ga on 17 Mar 2003 16:01 PST
Bruces, Without seeing your form, it is difficult to know which events to use, but I've written you some examples. Please ask for clarification if you need help getting your actual case to work as you want it to. The functions you want are the Domain Aggregate family. In this case, DCount and DLookup. These functions will retrieve information from other tables. They both take the same three arguments. 1. The name of the field in the external table 2. The name of the table 3. Criteria telling it which record to get the information from Note: In the code examples below, no lines of code should wrap. The answer box will often wrap lines, so you may need to unwrap them if you paste them into your own modules. Note: If you use the below code directly, make sure you adjust it to match the names of your fields, tables and controls. Here is an example of the functionality in the first part of your question: Private Sub txtJNumber_AfterUpdate() txtFirstName = DLookup("FirstName", "tblReferrals", "JNumber = " & txtJNumber) txtLastName = DLookup("LastName", "tblReferrals", "JNumber = " & txtJNumber) End Sub Here is an example of the functionality requested in the second part of your question: Private Sub txtLastName_Exit(Cancel As Integer) ' Declare some variables Dim intMatchCount As Integer Dim strWhere As String ' Make sure a last name has been entered If txtLastName.Value = "" Or IsNull(txtLastName.Value) Then MsgBox "You must enter a Last Name" Cancel = True Else ' Make sure a first name has been entered If txtFirstName.Value = "" Or IsNull(txtFirstName.Value) Then MsgBox "You must enter a First Name" txtFirstName.SetFocus Else ' Build the criteria strWhere = "FirstName = '" & txtFirstName.Value & "' AND LastName = '" & txtLastName.Value & "'" ' Check how many records match the First and Last Name entered intMatchCount = DCount("JNumber", "tblReferrals", strWhere) ' Do things based on the number of matches If intMatchCount = 0 Then MsgBox "No matching JNumber found." ElseIf intMatchCount = 1 Then ' Fill in JNumber txtJNumber = DLookup("JNumber", "tblReferrals", strWhere) Else MsgBox "You must enter the JNumber manually." End If End If End If End Sub Again, I took a guess at the events you would need to use. Please ask for clarification if you need help using the examples in your actual database. Additional Resources: Look in Access Help at DLookup and DCount MVPS Access FAQ http://www.mvps.org/access/ FAQ on Concatenating Values into Criteria http://www.mvps.org/access/forms/frm0001.htm Good luck with your Access project! - Hammer
rated this answer:
The very prompt response and the cautions about how lines of code can wrap in the Answer window were great. I was also impressed with the learning aids Hammer provided: comments in the code, an explanation of the functions used, and URLs for further inquiry.
|There are no comments at this time.|
If you feel that you have found inappropriate content, please let us know by emailing us at firstname.lastname@example.org with the question ID listed above. Thank you.
|Search Google Answers for|