• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1470
  • Last Modified:

Access 2007 - Lookup on form and update subform

I would like to have a form with a lookup that then displays the results in a sub form.

The Master form is created and the Record Source is a query, the Subform is created and the record source is a different query. The parent link master field and the child fields are linked.  This form works when a search is performed or one scrolls through the records.

What we would like to do is change the control named customer code to allow a user to type in the customer code and then tab or hit a button that would then move to the correct record (just like the search at the bottom of the Master form.
0
REIHELPDESK
Asked:
REIHELPDESK
  • 10
  • 8
1 Solution
 
thenelsonCommented:
I would use a combobox or listbox that lists all the possible choices in the table. Easier for the user as he only needs to type a portion of the name to select a name and there is less risk for having to deal with misspellings. You can use a textbox - just use the textbox name instead of the List/ComboBoxName.  You can use a textbox and a command button - just put the code of in the command button click event. If you want to jump to a record or filter the form when you open the form, just put the code in the form load event (also see: www.thenelson.name/#ReportFormTricks, "Open Last Record").  If you are using a combobox or listbox, method #1 is the easiest.

Four easy ways to jump to a record in a combobox or listbox but still be able to scroll through all the other records:

1) The combo box and list box wizard will do this for you automatically. In the first or second step (depending on you version of Access), choose the third option: "Find a record on my form based on the value selected in my combo box" This must be done on a BOUND form.  

2) Have the record ID number as one of the fields in the list/combo box and in the form (they can both be hidden).  The bound column of the list/combo box is the record ID field.  Then the On After Update Event of the list/combo box would look like:

Private Sub List/ComboBoxName_AfterUpdate()       'Use the name of your list/combo box
txtRecordID.SetFocus       'Use the name of your text box
DoCmd.FindRecord List/ComboBoxName       'Use the name of your list/combo box
End Sub

3) Again, the bound column of the list/combo box is the record ID field.

Private Sub List/ComboBoxName_AfterUpdate()       'Use the name of your list/combo box
Me.RecordSet.FindFirst "txtRecordID = " & List/ComboBoxName       'Use the name of your list/combo box and text box
End Sub

4)  Again, the bound column of the list/combo box is the record ID field.

Private Sub List/ComboBoxName_AfterUpdate()       'Use the name of your list/combo box
DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, List/ComboBoxName       'Use the name of your list/combo box and text box
End Sub

Two easy ways to filter the records to show only the record selected:

5) With the bound column of the list/combo box is the record ID field.

Private Sub List/ComboBoxName_AfterUpdate()       'Use the name of your list/combo box
Me.Filter = "txtRecordID = " & List/ComboBoxName       'Use the name of your list/combo box and text box
Me.FilterOn = True
End Sub

6) Again, the bound column of the list/combo box is the record ID field.

In the query that is the record source for the form, create a criteria like this:
[Forms].[YourFormName].[ComboBoxName]       'Use the name of your list/combo box and form name

Additional information:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q209537
http://allenbrowne.com/ser-03.html
http://www.rogersaccesslibrary.com/download3.asp?SampleName=ComboChoosesRecord.mdb
0
 
REIHELPDESKAuthor Commented:
Okay, and thanks.  

How do I validate that the subform is bound to the master? (I tried help first and didn't find it)
0
 
thenelsonCommented:
They should be linked if you have the master/child properties filled.  What problem are you having?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
REIHELPDESKAuthor Commented:
Okay, I changed the Master and Subform to be bound by the record ID and not the site code.  Well when I go through the records, clicking through them manually, I only get one record displayed in the subform; whereas, I was getting all the records.

Query 1 used for Master form
SELECT [Awards Table].[Award ID auto], [Site Table].Site_Code, [Site Table].Grant, [Site Table].[Site Name], [Site Table].[Previous Name], [Site Table].Address, [Site Table].Address_2, [Site Table].City, [Site Table].Zip, [Site Table].County
FROM [Site Table] LEFT JOIN [Awards Table] ON [Site Table].Site_Code = [Awards Table].Site_Code
WHERE ((([Awards Table].Site_Code) Is Not Null));

Query 1 user for Subform
SELECT [Awards Table].[Award ID auto], [Awards Table].Site_Code, [Awards Table].Type, [Awards Table].Rd, [Awards Table].[Amount Awarded], [Awards Table].[Award Date], [Awards Table].[Shared Award], [Awards Table].[Board Approval], [Awards Table].[Award Letter Sent], [Awards Table].[Award Expires Date]
FROM [Awards Table];

I'm not sure if I can use the Award ID Auto field.  The ID is different on each line item in the awards table.  The only common field between the Site Table and the Awards Table is the site code.  The site code refers to the Customer Number (basically).

Thanks, Rick
0
 
REIHELPDESKAuthor Commented:
Unless your talking using a different record ID to bound the master and sub forms.  Are  you?  If so, I'm not sure what that is.
0
 
thenelsonCommented:
What you are describing now is it not what you originally asked in your question. What is the problem you are having?
0
 
REIHELPDESKAuthor Commented:
Same problem as started.  Okay, I hammered on it a little and found this solution using a macro.

="[Site_Code] = " & Str(Nz([Screen].[ActiveControl],0))

I still want to get your solutions to work so I going to use.  I will update in a few minutes.
0
 
thenelsonCommented:
What control was the [ActiveControl]?
0
 
thenelsonCommented:
And are you trying to display all the records that [Site_Code] matches the [ActiveControl]?
0
 
REIHELPDESKAuthor Commented:
Okay, got it to work without a macro.

Private Sub Combo31_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Site_Code] = " & Str(Nz(Me![Combo31], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Question.  I tried to add a break point in the code, on line starting with "set", above so I could step through the code.  When I loaded the form and updated the number in the combo box and hit enter or tab the vb page didn't load.  I must be doing something wrong to prevent me from "breaking the code process" so I tab through the code.

What am I doing wrong?  I would like to be able to step through the code so I can debug.

Thanks,
Rick
0
 
REIHELPDESKAuthor Commented:
I even put a msgbox "Test" line of code in the above code before line starting with "Set" and it didn't fire.

What or how do I get it to work?

0
 
thenelsonCommented:
Let's simplify your code:
Private Sub Combo31_AfterUpdate()
    ' Find the record that matches the control.

Me.Recordset.FindFirst "[Site_Code] = " & Str(Nz(Me!Combo31, ""))

End Sub

Now you should see why you could not single step through your code. you essentially had one line that was doing all the work.
0
 
REIHELPDESKAuthor Commented:
That's great.  I like learning and thanks for helping.  Let me try it.

Should This work?
Private Sub Combo31_AfterUpdate()
    ' Find the record that matches the control.

msgbox "test"

Me.Recordset.FindFirst "[Site_Code] = " & Str(Nz(Me!Combo31, ""))

End Sub
0
 
REIHELPDESKAuthor Commented:
It worked this time.

Thanks, for all your help.
0
 
thenelsonCommented:
Sure, that will work to create a message box that says "test" before going to the record, but why?

Btw: I would rename Combo31 to something meaningful.

What does Combo31 return? - Please post some samples of the data.
0
 
REIHELPDESKAuthor Commented:
Combo31 only looks up the site code (aka customer number).

The Msgobx "Test" was for testing only. I wanted to be able to see if the control event was firing.

Thanks, for you help.  I have a long way to go on learning Access, but like what I see so far.

Do you also know about sharepoint and infopath?

0
 
REIHELPDESKAuthor Commented:
Was willing to help and answer all questions.  Great Contributor.
0
 
thenelsonCommented:
So you can rename Combo31 to txtSiteCode or txtCustomerNumber.  No difference to Access but it will make the code clearer for you if you need to debug or change it later. See naming conventions below.

"Do you also know about sharepoint and infopath?"
No, never used them.

"I wanted to be able to see if the control event was firing."
You can put a code break in the code. Select a line and press F9.

You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy computing!

Nelson


Naming conventions:
It is best to avoid spaces, underscores and other special characters in your naming of objects. For example: CountOfPeopleLive is just as easy to read as Count Of People_Live.  Objects named with special characters including spaces need to be placed in brackets for access to recognize them correctly. Here is an extreme example of the problems you will have using special characters in your names:
I named a textbox
!@#$%^&*()_+= -{}:;"'<,>?/|~
And had access create an event procedure.  Access converted the name of the textbox to:
Ctl_____________________________
so if the form had the name:
~!@#$%^&*()_+= -{}:;"'<,>?/|~
Referencing the textbox would be:
Forms!L_____________________________!Ctl_____________________________
a useful tip for someone who doesn't want someone else (probably even themselves) from reading their code.
And spaces will sometimes cause problems in vba references even after years of trouble free operation.

It is also a good idea to use a naming scheme such as leszynski naming conventions (see references). It makes it clearer what type of object you are naming and it reduces the risk of duplicate name problems (like a control name and its control source).

References:
Http://Www.Xoc.Net/standards/rvbanc.Asp
http://Www.Dhdurso.Org/articles/ms-access-naming.Html
http://Www.Acc-technology.Com/namconv.Htm
http://Www.Databasedev.Co.Uk/naming_conv.Html
http://En.Wikipedia.Org/wiki/leszynski_naming_convention
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 10
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now