[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access form with a subform - Unbound Listbox selection to update bound Customer Name used by subform

Posted on 2011-05-07
13
Medium Priority
?
2,180 Views
Last Modified: 2012-08-13
Hello experts!
I will show my stupidity of Access and VBA again with another question.

I have a series of queries that gets me to a summary of costs by Job and Job Cost Item.  I am creating a form that has a subform where the total costs on the Job To Date show as totals on a subform.

The main form is where the user selects which Job they are reviewing.  the Link Master and Child between the Main form and the Subform are Name

Instead of just using the navigation buttons to scroll from job to job I want the user to be able to select the job from a Listbox.

Using Access 2007 I created the Form and Subform and it brought the Field Name as a bound item on the form.  I then added an Unbound Listbox where I want the user to be able to select the Name from the list let it update the bound box [Name] and update the contents of the subform.

My unbound list box name is "lstJobCostSummaryJobSelector"

The bound items on the main form are:
[Name]
[Job]

The subform is "sfrmJobSummaryItemGroupDetail"

My Row Source for the "lstJobCostSummaryJobSelector" is
SELECT [qryQBCustomer].[NAME], [qryQBCustomer].[REFNUM], [qryQBCustomer].[Active Status T/F] FROM qryQBCustomer;

How do I get the bound item [Name] to update with the item selected in the list box?
0
Comment
Question by:wlwebb
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 600 total points
ID: 35713289
Well ... sounds like you can use the AfterUpdate event to update the Name control

Give your Name text box a name (Property) of say txtName,  then use this code:

Private Sub lstJobCostSummaryJobSelector_AfterUpdate()
   Me.txtName = Me.lstJobCostSummaryJobSelector
End Sub

Please avoid using Access Reserved Words like Name (and dozens of others) for object or field names, as it leads to endless confusion.

mx
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35713948
Here is some standard code I use to synchronize a form to an item selected in an unbound combo box.  In your case, you would need to modify it to synch the subform rather than the form itself:
Private Sub cboSelect_AfterUpdate()
'Created by Helen Feddema 29-Apr-2011
'Last modified by Helen Feddema 29-Apr-2011

On Error GoTo ErrorHandler

   Dim strSearch As String

   'For text IDs
   strSearch = "[______ID] = " & Chr$(39) & Me.ActiveControl.Value _
      & Chr$(39)

   'For numeric IDs
   strSearch = "[______ID] = " & Me.ActiveControl.Value

   'Find the record that matches the control
   Me.Recordset.FindFirst strSearch

ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35713951
Something like:

Set frmSub = Me![subData].Form

then instead of Me.Recordset use frmSub.Recordset
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.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 35713965
Some more key words to avoid for field names:  Date, Sum, Now, Type.  The problem is that if one of them is encountered in code, there is confusion about whether it is the field, or the function (or property, or whatever).  The same problem can occur if you give your bound controls the same names as their fields -- use the appropriate control prefixes to avoid confusion.
0
 

Author Comment

by:wlwebb
ID: 35717130
DatabaseMX

Thank you for the reply.  I left the office and came back today and saw your reply.  That part did in fact work but as suspected by Helen's reply the subform data does not update.  The main form fields update correctly which I ass-u-me-d would update the subform because that subform is based upon the master-child link.  My assumption was since the link didn't change, that the data being displayed in the sub would change when the main form field updated.

I attempted Helen's code.  However I don't understand it and I, being a newbie, don't understand enough to be able to modify it to get it to work.

*************************************
NOTE CHANGE IN FIELD NAME based upon both of yours comments
Field Name now = GLDataName

Textbox Name now = txtGLDataName

*********************************

My subform is named "sfrmJobSummaryItemGroupDetail"
Link Master Fields = GLDataName;Job
Link Child Fields = GLDataName;Job

Subform data fields to update:
Item
JCItemGrouping
Sum of Sum of Amount (GL data field is Amount)


Attached is the code I attempted based upon Helen's comment.


Private Sub lstJobCostSummaryJobSelector_AfterUpdate()
     Me.txtGLDataName = Me.lstJobCostSummaryJobSelector
     Me.txtJob = Me.lstJobCostSummaryJobSelector
     Set sfrmJobSummaryItemGroupDetail = Me![GLDataName].Form
     Set sfrmJobSummaryItemGroupDetail = Me![Job].Form
     

End Sub

Open in new window

0
 
LVL 26

Accepted Solution

by:
Nick67 earned 1400 total points
ID: 35717605
@Helen
< The same problem can occur if you give your bound controls the same names as their fields>

By default, Access 2003 gives all bound controls the same name as the field
In 7 years, it has NEVER gotten confused.
I use hungarian notation for unbound controls.

The caveat is that reports can be squirrelly
Intellisense may tell you that me!fieldname will work, but it doesn't; me!controlname is the only thing that works.
Where does the long-time developer fear of controls named after fields come from?

@wlwebb
<the Link Master and Child between the Main form and the Subform are Name>
Your form/subform should be linked on a primary key--and it SHOULDN'T be something 'updated' or 'updateable'
<Instead of just using the navigation buttons to scroll from job to job I want the user to be able to select the job from a Listbox.>

You aren't looking to 'update'  You are looking to do form navigation!

In the listbox afterupdate event

Private Sub lstJobCostSummaryJobSelector_AfterUpdate()
Dim rs As Recordset
Dim rsAdd As Recordset
Dim strCriteria As String
strCriteria = "[Name]  = " & chr(34)  Me.lstJobCostSummaryJobSelector & chr(34)
Me.Requery
Set rs = Me.RecordsetClone
rs.MoveLast
rs.FindLast strCriteria
Me.Bookmark = rs.Bookmark
End Sub
0
 

Author Comment

by:wlwebb
ID: 35737315
Nick67

Thank you for the comments.  I attempted adding your solution but it errors out on

strCriteria = "[Name]  = " & chr(34)  Me.lstJobCostSummaryJobSelector & chr(34)

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35738702
Ok,

First,
Above this line
strCriteria = "[Name]  = " & chr(34)  Me.lstJobCostSummaryJobSelector & chr(34)
Put
 msgbox Me.lstJobCostSummaryJobSelector

Change something in the listbox, and post what the messagebox shows.
I assumed ass-u-med that a [Name] value is what it would return, and that it would be a string
Post the listbox's rowsource too
0
 

Author Comment

by:wlwebb
ID: 35738842
Nick
Got it figured out now.  Needed an & after the first chr(34)
0
 

Author Comment

by:wlwebb
ID: 35738853
Corrected was:


Private Sub lstJobCostSummaryJobSelector_AfterUpdate()
Dim rs As Recordset
Dim rsAdd As Recordset
Dim strCriteria As String
strCriteria = "[Name]  = " & chr(34) &  Me.lstJobCostSummaryJobSelector & chr(34)
Me.Requery
Set rs = Me.RecordsetClone
rs.MoveLast
rs.FindLast strCriteria
Me.Bookmark = rs.Bookmark
End Sub

Open in new window

0
 
LVL 26

Expert Comment

by:Nick67
ID: 35738994
Yup
Sorry.
My bad for the typo.
0
 

Author Closing Comment

by:wlwebb
ID: 35739474
Thanks for all the help
0
 
LVL 26

Expert Comment

by:Nick67
ID: 35739497
No Problem.
Thanks for the points!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question