Listbox Select with VBA not updating dependants

[stevenblanc]

Hi folks,

So I have a subform whose 'Link Master Field' property is set to my listbox, 'lstMachines'.

When I click on an item in the listbox the subform automatically updates to reflect the child records.

However, when the items are updated in my listbox I use vba to select the first item in the listbox and in this case the subform is not updated to match the selection.

I tried requerying the subform after the vba select to no avail. Any ideas?


Steven

 

[rzw0wr]

Ok, I understand this I think.

When I click on an item in the listbox the subform automatically updates to reflect the child records

When you select an item from you list the subform displays the correct data.

I don't understand this.

However, when the items are updated in my listbox

Where is this data coming from? How is this related to selecting something from your list box?

This also has me guessing what is going on.

I use vba to select the first item in the listbox and in this case the subform is not updated to match the selection.

Can you tell us what it is you are trying to do?
Is this question about a subform or a list box?

Dale

 

[stevenblanc]

Dale,

Thanks for the reply.

The issue is: when I use vba to make a selection in the listbox my subform does not update.

If instead of using vba, I click to make the selection, the subform updates properly.

The listbox contains a list of machines relevant to a particular customer. If I change the customer on the mainform then the values in the listbox are updated. When this happens I also need the subform to update to the first item in the listbox.

I make the selection using:

 Me.lstMachines.Selected(0) = True

I don't see why selecting with vba would not trigger this update as the master field link should automatically update it when a change is made to the listbox whether it be by click or code.

EDIT: Just so we're clear, there are no problems updating the listbox values which is done by passing a SQL string the the box. Its just updating the subform to reflect the selected item.

 

[rzw0wr]

This is very confusing.
Why do you do this.

The issue is: when I use vba to make a selection in the listbox my subform does not update.

If you are going to use VBA, why make a list box?

Dale

 

[stevenblanc]

The subform data is tied to the listbox selection, i.e. master field link = lstmachines.

The purpose for the listbox is to allow the user to select the machine he wishes to view the details for on the subform. However, the machines listed in the listbox are only for the current customer. Therefore when the user changes the customer the listbox items need to update and I need the subform to reflect the first item in the new list.

AH wait... you mean I should just update the subform directly with VBA. If I do it that way wont that interfere with the link?

 

[rzw0wr]

OK. I think I am starting to see what you are saying.
The user makes the selection from the list box.
This is not a multiselect box, correct.

If the tables are set up correctly and the form/subfrom are bound correctly, the subfrom should update (display) without VBA.

But some how I don't think I fully understand what you are doing.

Dale

 

[rzw0wr]

The listbox contains a list of machines relevant to a particular customer. If I change the customer on the mainform then the values in the listbox are updated. When this happens I also need the subform to update to the first item in the listbox.

I re-read this several times.
Are you wanting to add new items to your list the select the new item and have the sub display the information about the new item.

Dale

 

[stevenblanc]

Ok.

There are three tables at work here:

tblCustomers - Customer Information
tblMachines - Machine Information - Linked to tblCustomers by CustomerID
tblService - Service History Information - Linked to tblMachines by MachineID

The form in question is the Service Entry Form. The user selects a customer using a popup form. The user selected is then inserted into the service entry form. The form then looks up the customer id in tblmachines and populates the listbox with the machines tied to that customer.

At this point the subform which contains the service history information from tblservice should be populated with the service history information. If the user wants to swap between machines tied to that customer, he can then use the listbox to quickly move back and forth. As it currently stands, the user clicking to select a machine correctly updates the subform.

However, when the user changes the customer and the listbox is updated the subform does not update. Does this make sense now?

 

[stevenblanc]

When the customer changes and the list updates (it updates just fine). I want the subform to update and display the service information for the first machine in the list. That does not work, if I then click on the first item, it updates.

 

[rzw0wr]

Yes, Very much.
Are you using any look up fields in your tables?
When selecting a new customer, the user is doing this from the popup, correct.

 

[stevenblanc]

Correct. The user selects from the popup and it inserts the customer id into txtcustomerid and calls its afterupdate event.

Public Sub txtCustomerID_AfterUpdate()
Dim intCustomerID As Integer
Dim strCustomerName As String

    intCustomerID = DLookup("CUST_ID", "tblCustomer", "CUST_SYID=" & Chr(34) & Me.txtCustomerID & Chr(34))
    strCustomerName = DLookup("CUST_NAME", "tblCustomer", "CUST_ID=" & intCustomerID)
    Me.lstMachines.RowSource = "SELECT * FROM tblMachines WHERE CUST_ID = " & intCustomerID & ";"
    Me.txtCustomerName = strCustomerName
    
    If Me.lstMachines.ListCount = 0 Then
        frmTransactionPart_Sub.Enabled = False
    Else
        frmTransactionPart_Sub.Enabled = True
        Me.lstMachines.Selected(0) = True
    End If

End Sub

This is where im trying to update the subform

 

[rzw0wr]

First, No quotes around a number.

intCustomerID = DLookup("CUST_ID", "tblCustomer", "CUST_SYID=" & Chr(34) & Me.txtCustomerID & Chr(34))

Dlookup("[CUST_ID]","tblCustomer","CUST_SYID= " & Me.TxtCustomerID)
Why did you change your CustomerID name on the form?

Me.lstMachines.RowSource = "SELECT * FROM tblMachines WHERE CUST_ID = " & intCustomerID & ";"

The & ";" is not needed.
try removing the double quotes around the txtCustomerID.
also try a refresh on your subform.
Dale

 

[stevenblanc]

1) CUST_SYID is not a number, it is a string.
2) Its not a bound field, and I know its a little inconsistent but I was porting code at one point from another one of my databases and it was easier to rename the textbox than find all the instances in the code.
3) That works fine without changing. Populating the listbox is not the issue. The listbox is populated perfectly.

Again the only issue is that the subform does not update with:

Me.lstMachines.Selected(0) = True

 

[rzw0wr]

This all looks funny to me.
I think this could be done in the WHERE clause of your command to open the Service Entry Form.

Dale

 

[rzw0wr]

Try a refresh on your sub.

 

[stevenblanc]

I dont open the service entry form.

It is a subform embedded in the same form as the listbox. It has the following properties:

Link Master Field: tblMachines
Link Child Field: MACH_ID ' which is the machine id

It cuts down on a lot of code. When I click it updates with no code. Just doesnt seem to work when i try to do it after the customer update.

 

[stevenblanc]

Neither refresh nor requery do anything.

 

[rzw0wr]

OK. Steven. I am at a lose here.
Sorry.
You may want to close this thread and post your question again.

Dale

 

[stevenblanc]

Thanks for the effort Dale.

Much appreciated. I'll repost tomorrow, I think I'm done for the night.

Cheers,


Steven