Rowsource Problem in Data Entry Mode

[gschimek]

In my database, which tracks insurance policies and claims, I have a form where I enter in the claim information. I'm trying to have a combo box where the rowsource only shows the types of policies that the client has, and not the rest. I've attached the following procedure to the Form Open event

Private Sub Form_Open(Cancel As Integer)
PolicyType.RowSource = "Select qryHeldPolicies.PolicyType " & _
            "FROM qryHeldPolicies " & _
            "WHERE qryHeldPolicies.CustomerID = " & Me![CustomerID] & " " & _
            "ORDER BY qryHeldPolicies.PolicyType ASC;"
End Sub

Now, that works perfectly when viewing the form in normal mode. But when I'm entering a new claim, I open the form in Data Entry mode. When I click the drop down arrow on the PolicyType combo box, I get an error saying "Syntax error (missing operator) in query expression 'qryHeldPolicies.CustomerID ='.

I populate the value of the CustomerID field when the form is opened. On the command button that opens the Claim Entry form, I have the code

    Forms!frmClaims.CustomerID = Forms!frmCustomers.CustomerID

which populates the CustomerID value from one form to the other.

What do I need to do to get this to work in Data Entry mode like it does in standard mode?

 

[spasticus]

i doubt this would make a difference but - do you set data entry mode then set the value of customerid or the other way round?

i was just thinking you might be changing the customer id for the first record then creating a new record with dataentry = true (or whatever it is)

 

[gschimek]

When I open the form, I set it to open in data entry mode, and then I set the value of the CustomerID field.

 

[gschimek]

OK, it looks like I've figured out a workaround, but I don't know why this way works and the other doesn't, so an explanation would be appreciated.

So, when the claims entry form is opened (by clicking a button on the main form, frmCustomers), the customerID field is populated from frmCustomers, as I stated above. I changed the code on the form_open event to this:

Private Sub Form_Open(Cancel As Integer)
PolicyType.RowSource = "Select qryHeldPolicies.PolicyType " & _
            "FROM qryHeldPolicies " & _
            "WHERE qryHeldPolicies.CustomerID = " & Forms!frmCustomers.CustomerID & " " & _
            "ORDER BY qryHeldPolicies.PolicyType ASC;"
End Sub

So instead of restricting the rowsource based on the value of the CustomerID field on the claims entry form (the one that just opened), it's restricted based on the value of the CustomerID field on the main form frmCustomers (which is also used to populate the CustomerID field on the claims entry form, so it's the same data in both spots.)

This appears to be working, though like I said, I don't know why it should matter. So if anybody has any insight, please, pass it on. Thanks.