Dynamic RecordSource

[morlan]

Setting the record source of a from dynamically

I have a form with a drop down. When the user changes the value in the combo the record source should change to a different table. However, when I run this code I get the following error:


Runtime Error 2107:

"The value you entered doesn't meet the validation rule defined for the field or control"


Here's the code:

Select Case Me.cmbCard.Column(0)

Case Is = "1"

'Set the record source to the AMEX table
Me.RecordSource = "tblAMEX_Main"

Exit Sub

End Case

 

[Mile-O]

But what's the validation rule on the combobox or the underlying field (if bound)

 

[dcx693]

I'm guessing you've made the combo box a bound field. From your description, it doesn't sound like it should be.

 

[morlan]

I have just created a new form and tried the code below but I still get an error

Private Sub Combo0_AfterUpdate()

Select Case Me.Combo0.Column(0)

Case Is = "1"


Me.RecordSource = "tblMain_Amex"


Exit Sub


Case Is = "2"


Me.RecordSource = "tblMBNA_Main"

Exit Sub

End Select

End Sub

====

Error 2101.

The setting you entered isn't valid for this property



Is it actually possible to change the record source dynamically to the name of a table?

 

[Mile-O]

I have just created a new form and tried the code below but I still get an error

Private Sub Combo0_AfterUpdate()

Error 2101.

The setting you entered isn't valid for this property

Is it actually possible to change the record source dynamically to the name of a table?

Do you have the LimitToList property set to yes?

And yes, it is possible to change source's dynamically.

 

[morlan]

Do you have the LimitToList property set to yes?

And yes, it is possible to change source's dynamically.

LimitToList is set to no.

 

[Mile-O]

Is the combobox bound to a field?

 

[morlan]

Is the combobox bound to a field?

Nope, it's unbound

 

[Mile-O]

Does it have a Validation Rule of any sort?

Are its values actually "1", "2", etc or 1, 2, etc

 

[morlan]

Does it have a Validation Rule of any sort?

Are its values actually "1", "2", etc or 1, 2, etc

No rules:

If I select the first value "1" from the combo, the debuger has the following part highlighted in yellow giving the 2101 error:

Select Case Me.Combo0.Column(0)

Case Is = "1"


Me.RecordSource = "tblMain_Amex"


Exit Sub


Case Is = "2"


Me.RecordSource = "tblMBNA_Main"

Exit Sub

End Select

End Sub

If I select the second item in the list "2", Me.RecordSource = "tblMBNA_Main" is highlighted

 

[Mile-O]

Try:

Me.RecordSource = "[tblMain_Amex]"

 

[morlan]

Try:

Me.RecordSource = "[tblMain_Amex]"

Same 2101 error with Me.RecordSource = "[tblMain_Amex]" highlighted

 

[morlan]

It doesn't seem to matter what I put in for the record source

Me.RecordSource = "[tblMain_Ame2x]" or
Me.RecordSource = "[arse]"

It comes up with same 2101 error. It's almost as if the recordsource cant be edited unless in design view

 

[Mile-O]

Can you post an example, then?

Or, is there something else on the form that's bound to something else concerning the current RecordSource?

 

[morlan]

Can you post an example, then?

Or, is there something else on the form that's bound to something else concerning the current RecordSource?

Do you have access 2000? Or must it be 97 format

 

[Mile-O]

2000 is fine.

 

[morlan]

ok here ya go

I've wasted half my day trying to get this to work

 

[Mile-O]

That worked fine for me as is.


Try compacting and repairing your database.

 

[morlan]

I did that!

Are you sure it's changing the record source? How do you know?

 

[Mile-O]

Are you sure it's changing the record source? How do you know?

Because I put 3 records in one table and 12 in the other.

When I used the combobox to select a different number the Navigation Buttons along the buttom would change to either 1 of 3 or 1 of 12