Problem with subform requery

[ASMELS]

This routine works fine in A97, but 2007 doesn't like it.
I have a form (AddressMgr) that has a datasheet subform (AddressSubform) that displays customer address data depending on address element selected in option group (PickGroup) on form. Technique is to build SQL record source and pass it to the subform then requery subform to display the records. Here's the code:

Private Sub PickGroup_AfterUpdate()
Dim MyCriteria As String

Select Case Me!PickGroup
Case Is = 1 'Same address
MyCriteria = "((Customer.Address1=[Forms]![AddressMgr]![Address1]))"
Case Is = 2 'Same city & State
MyCriteria = "((Customer.City=[Forms]![AddressMgr]![City]) AND (Customer.State=[Forms]![AddressMgr]![State]))"
Case Is = 3 'Same State
MyCriteria = "((Customer.State =[Forms]![AddressMgr]![State]))"
Case Is = 4 'Same post code
MyCriteria = "((Customer.Post =[Forms]![AddressMgr]![Post]))"
Case Is = 5 'Same country
MyCriteria = "((Customer.Country = [Forms]![AddressMgr]![Country]))"
End Select

'Set subform RecordSource and requery
Forms!AddressMgr!AddressSubform.Form.RecordSource = "SELECT DISTINCTROW Customer.* FROM Customer WHERE " & MyCriteria & " ORDER BY Customer.Name1;"
Forms!AddressMgr!AddressSubform.SetFocus

‘Try it both ways
Forms!AddressMgr!AddressSubform.Form.Requery
Forms!AddressMgr!AddressSubform.Requery

'Reset group to null
Me!PickGroup = 0

End Sub

SQL record source passes to subform just fine, but the requery doesn't seem to work. Any ideas?

ASMEL

 

[LisaWay]

I created a test form that works similarly to your form in Access 2007, and it worked correctly. The subform showed the correct information after setting its RecordSource; the calls to SetFocus and Requery are not necessary but did not cause the code to fail.

I'm not sure what's causing your problem. Do you have non-blank values entered in [AddressSubform].LinkChildFields or [AddressSubform].LinkMasterFields?

Regards,
Lisa

 

[ASMELS]

Hi Lisa:
Thanks for looking in and taking the time to address my issue. Both forms are unbound so you can't build a link between them. The SetFocus and Requery statements are holdovers from A97 which required them. Perhaps I should bind the forms and see if that help.
Thanks,
ASMELS

 

[PHV]

Both forms are unbound
Which kind of RecordSource may have an ubound form ????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ASMELS]

Hello PHV:

The main form is unbound and has a record source of:

SELECT DISTINCTROW qCustomerName.CustomerID AS Expr1, qCustomerName.Name1 AS Expr2, qCustomerName.City AS Expr3, qCustomerName.State AS Expr4 FROM qCustomerName ORDER BY qCustomerName.Name1;

The query upon which the recordsource is built is a union query that joins the current customer table with another table called CustomerOld that holds outdated names and address for the same customer.

SELECT Customer.CustomerID, Customer.Name1, Customer.Address1, Customer.City, Customer.State
FROM Customer;
UNION SELECT CustomerOld.CustomerID, CustomerOld.Name1, CustomerOld.Address1, CustomerOld.City, CustomerOld.State
FROM CustomerOld
ORDER BY Customer.Name1;

The CustomerID is shared between the two tables, e.g. CustomerNewName and CustomerOldName in both tables will have the same CustomerID so that a user may select an old name and/or address and get the new one.

There is a combobox that displays the results of the union query for the user to select the record of choice.

After a user selects a record (which is displayed on the main form) the option group permits display of other customers in the same geographical region.

Hope this explains my technique.

Lisa: I tried binding the forms, but that didn't seem to help either.

ASMELS

 

[TheAceMan1]

How are ya ASMELS . . .

Try:

[blue][AddressSubform].Form.Requery[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ASMELS]

Thanks for the suggestion Ace, but no joy here either.

ASMELS

 

[LisaWay]

I was able to make this work by doing the following:

(1) Change the main form's RecordSource to:

SELECT DISTINCTROW qCustomerName.CustomerID, qCustomerName.Name1, qCustomerName.City, qCustomerName.State FROM qCustomerName ORDER BY qCustomerName.Name1;

(2) Change the names of the text boxes on the main form to match the field names (for example, change "Expr1" to "CustomerID").

(3) In your code, change:

Forms!AddressMgr!AddressSubform.Form.RecordSource = "SELECT DISTINCTROW Customer.* FROM Customer WHERE " & MyCriteria & " ORDER BY Customer.Name1;"

To:

Forms!AddressMgr!AddressSubform.Form.RecordSource = "SELECT DISTINCTROW qCustomerName.* FROM qCustomerNameWHERE " & MyCriteria & " ORDER BY qCustomerName.Name1;"

Regards,
Lisa

 

[ASMELS]

Thanks Lisa:

Unfortunately your suggestions won't work.

Item (1):
The main form doesn't need a record source as it's unbound. The combo box control on the form must have the union query as its record source so that outdated names will appear on the selection list. Binding the form to the Customer table would defeat the purpose of the union query limiting the records to just the current Customer table.

Item (2):
The field names already exist as you suggest.

Item (3):
Changing the record source of the subform to the union query may give invalid results. The records to be displayed in the subform must come from the Customer table and not the CustomerOld table which comprise the union query. The field CustomerOld.CustomerName would not necessarily be found in the Customer table since the name may have changed which is why there is an entry in the CustomerOld table. A common CustomerID provides the mechanism for translation from the old name to the new.

This really isn't very difficult Access construction or code so what I'm thinking is that the form containers themselves may have been corrupted by importing from A97. I've seen this happen before and the only way out of it that I've seen is to just recreate the objects; I've just been too lazy to do it.

Thanks again for your continued efforts and assistance.

ASMELS

 

[LisaWay]

Sorry, I was confused by what you said in your 15 Dec 08 12:56 post:

The main form is unbound and has a record source of:

SELECT DISTINCTROW qCustomerName.CustomerID AS Expr1, qCustomerName.Name1 AS Expr2, qCustomerName.City AS Expr3, qCustomerName.State AS Expr4 FROM qCustomerName ORDER BY qCustomerName.Name1;

But now you say that:

Item (1):
The main form doesn't need a record source as it's unbound.

I guess you must mean that the main form is unbound, your combo box has the record source you quoted, and you copy the information from the record selected in the combo box into unbound text boxes on your main form. If that is correct, then what you are doing should work, and I agree that you must have some type of corruption in the form.

Regards,
Lisa