Hello,
I have 2 tables: 1) tbl - Issue_Codes and 2) tbl - Master Table
Step1 - I created a Search query using the "Loan Number" field in "Master Table". I named this query"qry - Loan Information Search."
Step 2 - I then created a form using this Search query. However, the "Issue_ID" field in this form was unable to give me the drop down box, so I deleted and add the existing fields (Issue_ID and Issue_type) from the Master Table itself. Now, I have a Search form with "Issue_ID" field having the drop down box.
Step 3 - Create an auto populate field (Issue_type) in the form. This is where I'm stuck and need help! I want to select the "Issue_ID" in the drop down box and the "Issue_type" field will auto populate based on the references in "Issue_Codes" table. How can I write a DLookUp in "Issue_type" Control Source? Please Help! Thank you so much!
Hmm. Can't seem to attach my dummy db zip file! The file is only 2.4 MB! What should I do here?
LH
Last edited by Dhoang25; 06-23-2011 at 02:23 PM.
I think rather than using the Control Source property in the form design view, you need to use the Issue_ID dropdown change event. Then you can set the source based on the value in your Issue_ID dropdown using vba.
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
Davegugg,
So I should go to "On Change" in Event property and enter a VBA code? Can I get the VBA code since I'm still new at this? Thanks a bunch!
LH
Thanks Dave! I made the changes in Event property and it works! Here is the VBA code that I used. Thanks!
Private Sub Issue_ID_Change()
Issue_type.Value = DLookup("Issue_type", "tbl - Issue_Codes", "Issue_ID ='" & Me.Issue_ID.Value & "'")
Me.Form.Refresh
End Sub
*Note - the '" above is apostrophe then quotation and "'" is quotation, apostrophe, quotation.
Actually, I'd use the After Update event. Then you'd want something like this:
Forms("Your_Form_Name").Issue_Type_Control.RowSource = "SELECT IssueType FROM Master_Table WHERE Issue_ID = " & Forms("Your_Form_Name").Issue_ID_Control.Value
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
Oops, I see I just missed you. You can use a DLookup to return one record, but if you want to return more than one record, like for a listbox, you would want to use a sql statement like my example in post 5.
Glad you were able to get it!
Is your code running too slowly?
Does your workbook or database have a bunch of duplicate pieces of data?
Have a look at this article to learn the best ways to set up your projects.
It will save both time and effort in the long run!
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks