how to change the record source by using combo box

  • Thread starter Thread starter selinaT
  • Start date Start date
S

selinaT

i have created a combo box on a form A, can let me to select the table and
display the record on the form B.
i don't know how to write coding, so can teach me step by step?

Example:
i have TABLE A, B and C, all is same field but differance record.
so, at the Form A i create combo box for me to select the table and display
the record on Form B.
Then the Form B will auto change the record source.
 
I question the need for 3 tables with the same fields, but that is for
another time. Let's say your combo name is cboMyCombo. The code might look
something like:

Sub cboMyCombo_AfterUpdate()
Select Case Me.cboMyCombo
Case "TABLE A"
Forms!FormB.Recordsource = "[TABLE A]"
Case "TABLE B"
Forms!FormB.Recordsource = "[TABLE B]"
Case "TABLE C"
Forms!FormB.Recordsource = "[TABLE C]"
End Select
End Sub

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
cannot work!
run-time error '2450'
cannot find formB....
it highlight this line: Forms!FormB.Recordsource = "[TABLE A]"

but i already create FormB with the table fields.

i want is when i select TABLE A then FormB will open and show the TABLE A
record.
I question the need for 3 tables with the same fields, but that is for
another time. Let's say your combo name is cboMyCombo. The code might look
something like:

Sub cboMyCombo_AfterUpdate()
Select Case Me.cboMyCombo
Case "TABLE A"
Forms!FormB.Recordsource = "[TABLE A]"
Case "TABLE B"
Forms!FormB.Recordsource = "[TABLE B]"
Case "TABLE C"
Forms!FormB.Recordsource = "[TABLE C]"
End Select
End Sub

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
i have created a combo box on a form A, can let me to select the table and
display the record on the form B.
Click to expand...
[quoted text clipped - 5 lines]
the record on Form B.
Then the Form B will auto change the record source.
Click to expand...
Click to expand...
 
FormB must be open. You may have to add:

Forms!FormB.Requery

after the End Select statement if the form doesn't requery when the code
runs.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

selinaT via AccessMonster.com said:
cannot work!
run-time error '2450'
cannot find formB....
it highlight this line: Forms!FormB.Recordsource = "[TABLE A]"

but i already create FormB with the table fields.

i want is when i select TABLE A then FormB will open and show the TABLE A
record.
I question the need for 3 tables with the same fields, but that is for
another time. Let's say your combo name is cboMyCombo. The code might look
something like:

Sub cboMyCombo_AfterUpdate()
Select Case Me.cboMyCombo
Case "TABLE A"
Forms!FormB.Recordsource = "[TABLE A]"
Case "TABLE B"
Forms!FormB.Recordsource = "[TABLE B]"
Case "TABLE C"
Forms!FormB.Recordsource = "[TABLE C]"
End Select
End Sub

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
i have created a combo box on a form A, can let me to select the table and
display the record on the form B.
Click to expand...
[quoted text clipped - 5 lines]
the record on Form B.
Then the Form B will auto change the record source.
Click to expand...
Click to expand...
Click to expand...
 
Like this:

Sub cboMyCombo_AfterUpdate()
Select Case Me.cboMyCombo
Case "TABLE A"
Forms!FormB.Recordsource = "[TABLE A]"
Case "TABLE B"
Forms!FormB.Recordsource = "[TABLE B]"
Case "TABLE C"
Forms!FormB.Recordsource = "[TABLE C]"
End Select

Forms!FormB.Requery

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


selinaT via AccessMonster.com said:
hi,
where i need to add Forms!FormB.Requery?

and

where to put the code runs?

FormB must be open. You may have to add:

Forms!FormB.Requery

after the End Select statement if the form doesn't requery when the code
runs.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
cannot work!
run-time error '2450'
Click to expand...
[quoted text clipped - 33 lines]
the record on Form B.
Then the Form B will auto change the record source.
Click to expand...
Click to expand...
Click to expand...
 
i have try, but cannot work.

the error message is

run-time error '2101'
the setting you entered isn't valid for this property.
Like this:

Sub cboMyCombo_AfterUpdate()
Select Case Me.cboMyCombo
Case "TABLE A"
Forms!FormB.Recordsource = "[TABLE A]"
Case "TABLE B"
Forms!FormB.Recordsource = "[TABLE B]"
Case "TABLE C"
Forms!FormB.Recordsource = "[TABLE C]"
End Select

Forms!FormB.Requery

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
hi,
where i need to add Forms!FormB.Requery?
Click to expand...
[quoted text clipped - 21 lines]
Click to expand...
 
I'll try and see what's wrong. Compact and zip your database and send it to:

arvinm at accessmvp dot com

make the obvious corrections.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


selinaT via AccessMonster.com said:
i have try, but cannot work.

the error message is

run-time error '2101'
the setting you entered isn't valid for this property.
Like this:

Sub cboMyCombo_AfterUpdate()
Select Case Me.cboMyCombo
Case "TABLE A"
Forms!FormB.Recordsource = "[TABLE A]"
Case "TABLE B"
Forms!FormB.Recordsource = "[TABLE B]"
Case "TABLE C"
Forms!FormB.Recordsource = "[TABLE C]"
End Select

Forms!FormB.Requery

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
hi,
where i need to add Forms!FormB.Requery?
Click to expand...
[quoted text clipped - 21 lines]
the record on Form B.
Then the Form B will auto change the record source.
Click to expand...
Click to expand...
Click to expand...
 
Arvin Meyer [MVP] wrote:
Can this be done for a subform?

I've tried this with an Option Group Frame but the recordsource doesn't
appear to get reset...

syntax

Select Case etc
Forms!FrmPosting!Posting.RecordSource ="acctpost"
End Case etc
Forms!FrmPosting!Posting.requery

thanks

Wendy



Like this:

Sub cboMyCombo_AfterUpdate()
Select Case Me.cboMyCombo
Case "TABLE A"
Forms!FormB.Recordsource = "[TABLE A]"
Case "TABLE B"
Forms!FormB.Recordsource = "[TABLE B]"
Case "TABLE C"
Forms!FormB.Recordsource = "[TABLE C]"
End Select

Forms!FormB.Requery

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


hi,
where i need to add Forms!FormB.Requery?

and

where to put the code runs?

FormB must be open. You may have to add:

Forms!FormB.Requery

after the End Select statement if the form doesn't requery when the code
runs.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


cannot work!
run-time error '2450'

[quoted text clipped - 33 lines]

the record on Form B.
Then the Form B will auto change the record source.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top