Using a form to pull record(s) into a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it possible to have combo box pull up a value and then a query populate a
subform located on the same form?

The subform would be based on a query with a field having the combo box as
its criteria.

Got any ideas?
 
Michaela said:
Is it possible to have combo box pull up a value and then a query populate a
subform located on the same form?

The subform would be based on a query with a field having the combo box as
its criteria.
Click to expand...


You might be able to just set the subform control's
LinkMaster property to the combo box and LinkChild to the
field you want to filter.

If that won't work for you, then set the subform's
RecordSource to a constructed SQL statement. E.g.

strSQL = SELECT * FROM table WHERE field=" & Mecombo
Me subformcontrol.Form.RecordSource = strSQL

The Filter property is also supposed to be useful for this
kind of thing, but it has too many "issues" for me to
recommend it.
 
I couldn't get the Master/Child links to work.

I'm new at SQL statements. The main form is called
"frm-Bid_Information-Master". The subform is called
"frm-Bid_Information-Subform". The table the main form is based on is
"Event - Auction Items". The field that I want to pull up the records by is
called "Which Auction?"

How would these fit into your SQL statement?

I appreciate your help with this!
 
OK, now that I've thought about it some more, scrap my
previous suggestions and do this using your own idea of
basing the subform on a saved query with the combo box as
its criteria.

I don't know what the subform's record source table/query
looks like, but maybe this will get you started:

SELECT *
FROM subformtable
WHERE [Which Auction?] =
Forms![frm-Bid_Information-Master].combobox

You'll have to change subformtable to the name of the
table(?) that contains the subform's data. Also change
combobox to the name of the combo box on the main form.

You will also have to add a line of code to the combo box's
AfterUpdate event procedure:
Me.[frm-Bid_Information-Subform].Form.Requery
and the same line would also have to go in the form's
Current event procedure.
 
Remember I'm a beginner!

I typed in SELECT * FROM [Event - Auction Items] WHERE [Which
Auction?]=Forms![frm-Bid_Informaton-Master]![Auction Selection] in the
Record Source properties blank for the subform.

I typed in the following into AfterUpDate for the combo box and in the main
form's On Current field:
Me![frm-Bid_Information-Subform].Form.Requery

The results:
When I open the form I get a message box prompting me for a parameter:
Forms!frm-Bid_Informaton-Master!Auction Selection

Then I get a message that Access can't find the macro:
Me![frm-Bid_Information-Subform]

What am I doing wrong? My syntax? Where I'm typing in the info?

Thanks for your input!
M
Marshall Barton said:
OK, now that I've thought about it some more, scrap my
previous suggestions and do this using your own idea of
basing the subform on a saved query with the combo box as
its criteria.

I don't know what the subform's record source table/query
looks like, but maybe this will get you started:

SELECT *
FROM subformtable
WHERE [Which Auction?] =
Forms![frm-Bid_Information-Master].combobox

You'll have to change subformtable to the name of the
table(?) that contains the subform's data. Also change
combobox to the name of the combo box on the main form.

You will also have to add a line of code to the combo box's
AfterUpdate event procedure:
Me.[frm-Bid_Information-Subform].Form.Requery
and the same line would also have to go in the form's
Current event procedure.
--
Marsh
MVP [MS Access]


I couldn't get the Master/Child links to work.

I'm new at SQL statements. The main form is called
"frm-Bid_Information-Master". The subform is called
"frm-Bid_Information-Subform". The table the main form is based on is
"Event - Auction Items". The field that I want to pull up the records by is
called "Which Auction?"

How would these fit into your SQL statement?

I appreciate your help with this!
Click to expand...
Click to expand...
 
It sounds like you put the requery in the AfterUpdate
property on the form's property sheet. The property should
have [Event Procedure]. If you them click the little [...]
button way to the right of the property, the form's code
module should open to the AfterUpdate **procedure** where
you can enter the line of code.

The parameter prompt may mean that the subform **control**
is not named the same as the (sub)form object it is
displaying. Double check the name of the control on the
main form.


I typed in SELECT * FROM [Event - Auction Items] WHERE [Which
Auction?]=Forms![frm-Bid_Informaton-Master]![Auction Selection] in the
Record Source properties blank for the subform.

I typed in the following into AfterUpDate for the combo box and in the main
form's On Current field:
Me![frm-Bid_Information-Subform].Form.Requery

The results:
When I open the form I get a message box prompting me for a parameter:
Forms!frm-Bid_Informaton-Master!Auction Selection

Then I get a message that Access can't find the macro:
Me![frm-Bid_Information-Subform]

What am I doing wrong? My syntax? Where I'm typing in the info?


Marshall Barton said:
OK, now that I've thought about it some more, scrap my
previous suggestions and do this using your own idea of
basing the subform on a saved query with the combo box as
its criteria.

I don't know what the subform's record source table/query
looks like, but maybe this will get you started:

SELECT *
FROM subformtable
WHERE [Which Auction?] =
Forms![frm-Bid_Information-Master].combobox

You'll have to change subformtable to the name of the
table(?) that contains the subform's data. Also change
combobox to the name of the combo box on the main form.

You will also have to add a line of code to the combo box's
AfterUpdate event procedure:
Me.[frm-Bid_Information-Subform].Form.Requery
and the same line would also have to go in the form's
Current event procedure.
Click to expand...
Click to expand...
 
Thank you for your patience!

I had the requery statement in the right place - for the AfterUpdate event.
I took it out of the Current Event for the form and it went right through.

I've kept the names the same for "Which Auction?" for each form and subform.

I have previously put binoculars on the top of a form and then added
subforms which will pull up answers from the search, but I was looking for a
way to circumvent having to have people use another form.

Any other suggestions? Should I make a simple database with the basic
structure and send it to you?

Thanks for your help!
M

Marshall Barton said:
It sounds like you put the requery in the AfterUpdate
property on the form's property sheet. The property should
have [Event Procedure]. If you them click the little [...]
button way to the right of the property, the form's code
module should open to the AfterUpdate **procedure** where
you can enter the line of code.

The parameter prompt may mean that the subform **control**
is not named the same as the (sub)form object it is
displaying. Double check the name of the control on the
main form.


I typed in SELECT * FROM [Event - Auction Items] WHERE [Which
Auction?]=Forms![frm-Bid_Informaton-Master]![Auction Selection] in the
Record Source properties blank for the subform.

I typed in the following into AfterUpDate for the combo box and in the main
form's On Current field:
Me![frm-Bid_Information-Subform].Form.Requery

The results:
When I open the form I get a message box prompting me for a parameter:
Forms!frm-Bid_Informaton-Master!Auction Selection

Then I get a message that Access can't find the macro:
Me![frm-Bid_Information-Subform]

What am I doing wrong? My syntax? Where I'm typing in the info?


Marshall Barton said:
OK, now that I've thought about it some more, scrap my
previous suggestions and do this using your own idea of
basing the subform on a saved query with the combo box as
its criteria.

I don't know what the subform's record source table/query
looks like, but maybe this will get you started:

SELECT *
FROM subformtable
WHERE [Which Auction?] =
Forms![frm-Bid_Information-Master].combobox

You'll have to change subformtable to the name of the
table(?) that contains the subform's data. Also change
combobox to the name of the combo box on the main form.

You will also have to add a line of code to the combo box's
AfterUpdate event procedure:
Me.[frm-Bid_Information-Subform].Form.Requery
and the same line would also have to go in the form's
Current event procedure.
Click to expand...
Click to expand...
Click to expand...
 
It still sounds like you have some of the names misspelled,
unbracketted, or just wrong.

I don't really have time for analyzing someone else's
database. It's tough enough do that for paying clients. If
you're totally stuck and can tolerate an indefinite wait for
me to find time to look at it, then compact the database,
Zip it to to under one MB and send it. Hopefully, I can get
to it over the weekend.
--
Marsh
MVP [MS Access]


I had the requery statement in the right place - for the AfterUpdate event.
I took it out of the Current Event for the form and it went right through.

I've kept the names the same for "Which Auction?" for each form and subform.

I have previously put binoculars on the top of a form and then added
subforms which will pull up answers from the search, but I was looking for a
way to circumvent having to have people use another form.

Any other suggestions? Should I make a simple database with the basic
structure and send it to you?


Marshall Barton said:
It sounds like you put the requery in the AfterUpdate
property on the form's property sheet. The property should
have [Event Procedure]. If you them click the little [...]
button way to the right of the property, the form's code
module should open to the AfterUpdate **procedure** where
you can enter the line of code.

The parameter prompt may mean that the subform **control**
is not named the same as the (sub)form object it is
displaying. Double check the name of the control on the
main form.


I typed in SELECT * FROM [Event - Auction Items] WHERE [Which
Auction?]=Forms![frm-Bid_Informaton-Master]![Auction Selection] in the
Record Source properties blank for the subform.

I typed in the following into AfterUpDate for the combo box and in the main
form's On Current field:
Me![frm-Bid_Information-Subform].Form.Requery

The results:
When I open the form I get a message box prompting me for a parameter:
Forms!frm-Bid_Informaton-Master!Auction Selection

Then I get a message that Access can't find the macro:
Me![frm-Bid_Information-Subform]

What am I doing wrong? My syntax? Where I'm typing in the info?


:
OK, now that I've thought about it some more, scrap my
previous suggestions and do this using your own idea of
basing the subform on a saved query with the combo box as
its criteria.

I don't know what the subform's record source table/query
looks like, but maybe this will get you started:

SELECT *
FROM subformtable
WHERE [Which Auction?] =
Forms![frm-Bid_Information-Master].combobox

You'll have to change subformtable to the name of the
table(?) that contains the subform's data. Also change
combobox to the name of the combo box on the main form.

You will also have to add a line of code to the combo box's
AfterUpdate event procedure:
Me.[frm-Bid_Information-Subform].Form.Requery
and the same line would also have to go in the form's
Current event procedure.
Click to expand...
Click to expand...
Click to expand...
 
I understand what you mean and I appreciate your offer. How do I send it to
you?
M

Marshall Barton said:
It still sounds like you have some of the names misspelled,
unbracketted, or just wrong.

I don't really have time for analyzing someone else's
database. It's tough enough do that for paying clients. If
you're totally stuck and can tolerate an indefinite wait for
me to find time to look at it, then compact the database,
Zip it to to under one MB and send it. Hopefully, I can get
to it over the weekend.
--
Marsh
MVP [MS Access]


I had the requery statement in the right place - for the AfterUpdate event.
I took it out of the Current Event for the form and it went right through.

I've kept the names the same for "Which Auction?" for each form and subform.

I have previously put binoculars on the top of a form and then added
subforms which will pull up answers from the search, but I was looking for a
way to circumvent having to have people use another form.

Any other suggestions? Should I make a simple database with the basic
structure and send it to you?


Marshall Barton said:
It sounds like you put the requery in the AfterUpdate
property on the form's property sheet. The property should
have [Event Procedure]. If you them click the little [...]
button way to the right of the property, the form's code
module should open to the AfterUpdate **procedure** where
you can enter the line of code.

The parameter prompt may mean that the subform **control**
is not named the same as the (sub)form object it is
displaying. Double check the name of the control on the
main form.



Michaela wrote:
I typed in SELECT * FROM [Event - Auction Items] WHERE [Which
Auction?]=Forms![frm-Bid_Informaton-Master]![Auction Selection] in the
Record Source properties blank for the subform.

I typed in the following into AfterUpDate for the combo box and in the main
form's On Current field:
Me![frm-Bid_Information-Subform].Form.Requery

The results:
When I open the form I get a message box prompting me for a parameter:
Forms!frm-Bid_Informaton-Master!Auction Selection

Then I get a message that Access can't find the macro:
Me![frm-Bid_Information-Subform]

What am I doing wrong? My syntax? Where I'm typing in the info?


:
OK, now that I've thought about it some more, scrap my
previous suggestions and do this using your own idea of
basing the subform on a saved query with the combo box as
its criteria.

I don't know what the subform's record source table/query
looks like, but maybe this will get you started:

SELECT *
FROM subformtable
WHERE [Which Auction?] =
Forms![frm-Bid_Information-Master].combobox

You'll have to change subformtable to the name of the
table(?) that contains the subform's data. Also change
combobox to the name of the combo box on the main form.

You will also have to add a line of code to the combo box's
AfterUpdate event procedure:
Me.[frm-Bid_Information-Subform].Form.Requery
and the same line would also have to go in the form's
Current event procedure.
Click to expand...
Click to expand...
Click to expand...
 
Just attach it to an email and send it to the address I'm
using for the newsgroups.
--
Marsh
MVP [MS Access]


I understand what you mean and I appreciate your offer. How do I send it to
you?

Marshall Barton said:
It still sounds like you have some of the names misspelled,
unbracketted, or just wrong.

I don't really have time for analyzing someone else's
database. It's tough enough do that for paying clients. If
you're totally stuck and can tolerate an indefinite wait for
me to find time to look at it, then compact the database,
Zip it to to under one MB and send it. Hopefully, I can get
to it over the weekend.

I had the requery statement in the right place - for the AfterUpdate event.
I took it out of the Current Event for the form and it went right through.

I've kept the names the same for "Which Auction?" for each form and subform.

I have previously put binoculars on the top of a form and then added
subforms which will pull up answers from the search, but I was looking for a
way to circumvent having to have people use another form.

Any other suggestions? Should I make a simple database with the basic
structure and send it to you?


:
It sounds like you put the requery in the AfterUpdate
property on the form's property sheet. The property should
have [Event Procedure]. If you them click the little [...]
button way to the right of the property, the form's code
module should open to the AfterUpdate **procedure** where
you can enter the line of code.

The parameter prompt may mean that the subform **control**
is not named the same as the (sub)form object it is
displaying. Double check the name of the control on the
main form.



Michaela wrote:
I typed in SELECT * FROM [Event - Auction Items] WHERE [Which
Auction?]=Forms![frm-Bid_Informaton-Master]![Auction Selection] in the
Record Source properties blank for the subform.

I typed in the following into AfterUpDate for the combo box and in the main
form's On Current field:
Me![frm-Bid_Information-Subform].Form.Requery

The results:
When I open the form I get a message box prompting me for a parameter:
Forms!frm-Bid_Informaton-Master!Auction Selection

Then I get a message that Access can't find the macro:
Me![frm-Bid_Information-Subform]

What am I doing wrong? My syntax? Where I'm typing in the info?


:
OK, now that I've thought about it some more, scrap my
previous suggestions and do this using your own idea of
basing the subform on a saved query with the combo box as
its criteria.

I don't know what the subform's record source table/query
looks like, but maybe this will get you started:

SELECT *
FROM subformtable
WHERE [Which Auction?] =
Forms![frm-Bid_Information-Master].combobox

You'll have to change subformtable to the name of the
table(?) that contains the subform's data. Also change
combobox to the name of the combo box on the main form.

You will also have to add a line of code to the combo box's
AfterUpdate event procedure:
Me.[frm-Bid_Information-Subform].Form.Requery
and the same line would also have to go in the form's
Current event procedure.
Click to expand...
Click to expand...
Click to expand...
 
Where do I find addresses? Sigh! I am so new at this!
M

Marshall Barton said:
Just attach it to an email and send it to the address I'm
using for the newsgroups.
--
Marsh
MVP [MS Access]


I understand what you mean and I appreciate your offer. How do I send it to
you?

Marshall Barton said:
It still sounds like you have some of the names misspelled,
unbracketted, or just wrong.

I don't really have time for analyzing someone else's
database. It's tough enough do that for paying clients. If
you're totally stuck and can tolerate an indefinite wait for
me to find time to look at it, then compact the database,
Zip it to to under one MB and send it. Hopefully, I can get
to it over the weekend.


Michaela wrote:
I had the requery statement in the right place - for the AfterUpdate event.
I took it out of the Current Event for the form and it went right through.

I've kept the names the same for "Which Auction?" for each form and subform.

I have previously put binoculars on the top of a form and then added
subforms which will pull up answers from the search, but I was looking for a
way to circumvent having to have people use another form.

Any other suggestions? Should I make a simple database with the basic
structure and send it to you?


:
It sounds like you put the requery in the AfterUpdate
property on the form's property sheet. The property should
have [Event Procedure]. If you them click the little [...]
button way to the right of the property, the form's code
module should open to the AfterUpdate **procedure** where
you can enter the line of code.

The parameter prompt may mean that the subform **control**
is not named the same as the (sub)form object it is
displaying. Double check the name of the control on the
main form.



Michaela wrote:
I typed in SELECT * FROM [Event - Auction Items] WHERE [Which
Auction?]=Forms![frm-Bid_Informaton-Master]![Auction Selection] in the
Record Source properties blank for the subform.

I typed in the following into AfterUpDate for the combo box and in the main
form's On Current field:
Me![frm-Bid_Information-Subform].Form.Requery

The results:
When I open the form I get a message box prompting me for a parameter:
Forms!frm-Bid_Informaton-Master!Auction Selection

Then I get a message that Access can't find the macro:
Me![frm-Bid_Information-Subform]

What am I doing wrong? My syntax? Where I'm typing in the info?


:
OK, now that I've thought about it some more, scrap my
previous suggestions and do this using your own idea of
basing the subform on a saved query with the combo box as
its criteria.

I don't know what the subform's record source table/query
looks like, but maybe this will get you started:

SELECT *
FROM subformtable
WHERE [Which Auction?] =
Forms![frm-Bid_Information-Master].combobox

You'll have to change subformtable to the name of the
table(?) that contains the subform's data. Also change
combobox to the name of the combo box on the main form.

You will also have to add a line of code to the combo box's
AfterUpdate event procedure:
Me.[frm-Bid_Information-Subform].Form.Requery
and the same line would also have to go in the form's
Current event procedure.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, can't help with whatever reader program you're using.

On mine (Agent), I display headers and look at the FROM
field or use the Reply via email button.

Regardless of all that folderol, send it to:
(e-mail address removed)
--
Marsh
MVP [MS Access]

Where do I find addresses? Sigh! I am so new at this!


Marshall Barton said:
Just attach it to an email and send it to the address I'm
using for the newsgroups.

I understand what you mean and I appreciate your offer. How do I send it to
you?

:
It still sounds like you have some of the names misspelled,
unbracketted, or just wrong.

I don't really have time for analyzing someone else's
database. It's tough enough do that for paying clients. If
you're totally stuck and can tolerate an indefinite wait for
me to find time to look at it, then compact the database,
Zip it to to under one MB and send it. Hopefully, I can get
to it over the weekend.


Michaela wrote:
I had the requery statement in the right place - for the AfterUpdate event.
I took it out of the Current Event for the form and it went right through.

I've kept the names the same for "Which Auction?" for each form and subform.

I have previously put binoculars on the top of a form and then added
subforms which will pull up answers from the search, but I was looking for a
way to circumvent having to have people use another form.

Any other suggestions? Should I make a simple database with the basic
structure and send it to you?


:
It sounds like you put the requery in the AfterUpdate
property on the form's property sheet. The property should
have [Event Procedure]. If you them click the little [...]
button way to the right of the property, the form's code
module should open to the AfterUpdate **procedure** where
you can enter the line of code.

The parameter prompt may mean that the subform **control**
is not named the same as the (sub)form object it is
displaying. Double check the name of the control on the
main form.



Michaela wrote:
I typed in SELECT * FROM [Event - Auction Items] WHERE [Which
Auction?]=Forms![frm-Bid_Informaton-Master]![Auction Selection] in the
Record Source properties blank for the subform.

I typed in the following into AfterUpDate for the combo box and in the main
form's On Current field:
Me![frm-Bid_Information-Subform].Form.Requery

The results:
When I open the form I get a message box prompting me for a parameter:
Forms!frm-Bid_Informaton-Master!Auction Selection

Then I get a message that Access can't find the macro:
Me![frm-Bid_Information-Subform]

What am I doing wrong? My syntax? Where I'm typing in the info?


:
OK, now that I've thought about it some more, scrap my
previous suggestions and do this using your own idea of
basing the subform on a saved query with the combo box as
its criteria.

I don't know what the subform's record source table/query
looks like, but maybe this will get you started:

SELECT *
FROM subformtable
WHERE [Which Auction?] =
Forms![frm-Bid_Information-Master].combobox

You'll have to change subformtable to the name of the
table(?) that contains the subform's data. Also change
combobox to the name of the combo box on the main form.

You will also have to add a line of code to the combo box's
AfterUpdate event procedure:
Me.[frm-Bid_Information-Subform].Form.Requery
and the same line would also have to go in the form's
Current event procedure.
Click to expand...
Click to expand...
Click to expand...
 
I just emailed you a file with the problem. It's about 1.5 Mb.

Again, thank you for your help.

Michaela

Marshall Barton said:
Sorry, can't help with whatever reader program you're using.

On mine (Agent), I display headers and look at the FROM
field or use the Reply via email button.

Regardless of all that folderol, send it to:
(e-mail address removed)
--
Marsh
MVP [MS Access]

Where do I find addresses? Sigh! I am so new at this!


Marshall Barton said:
Just attach it to an email and send it to the address I'm
using for the newsgroups.


Michaela wrote:
I understand what you mean and I appreciate your offer. How do I send it to
you?

:
It still sounds like you have some of the names misspelled,
unbracketted, or just wrong.

I don't really have time for analyzing someone else's
database. It's tough enough do that for paying clients. If
you're totally stuck and can tolerate an indefinite wait for
me to find time to look at it, then compact the database,
Zip it to to under one MB and send it. Hopefully, I can get
to it over the weekend.


Michaela wrote:
I had the requery statement in the right place - for the AfterUpdate event.
I took it out of the Current Event for the form and it went right through.

I've kept the names the same for "Which Auction?" for each form and subform.

I have previously put binoculars on the top of a form and then added
subforms which will pull up answers from the search, but I was looking for a
way to circumvent having to have people use another form.

Any other suggestions? Should I make a simple database with the basic
structure and send it to you?


:
It sounds like you put the requery in the AfterUpdate
property on the form's property sheet. The property should
have [Event Procedure]. If you them click the little [...]
button way to the right of the property, the form's code
module should open to the AfterUpdate **procedure** where
you can enter the line of code.

The parameter prompt may mean that the subform **control**
is not named the same as the (sub)form object it is
displaying. Double check the name of the control on the
main form.



Michaela wrote:
I typed in SELECT * FROM [Event - Auction Items] WHERE [Which
Auction?]=Forms![frm-Bid_Informaton-Master]![Auction Selection] in the
Record Source properties blank for the subform.

I typed in the following into AfterUpDate for the combo box and in the main
form's On Current field:
Me![frm-Bid_Information-Subform].Form.Requery

The results:
When I open the form I get a message box prompting me for a parameter:
Forms!frm-Bid_Informaton-Master!Auction Selection

Then I get a message that Access can't find the macro:
Me![frm-Bid_Information-Subform]

What am I doing wrong? My syntax? Where I'm typing in the info?


:
OK, now that I've thought about it some more, scrap my
previous suggestions and do this using your own idea of
basing the subform on a saved query with the combo box as
its criteria.

I don't know what the subform's record source table/query
looks like, but maybe this will get you started:

SELECT *
FROM subformtable
WHERE [Which Auction?] =
Forms![frm-Bid_Information-Master].combobox

You'll have to change subformtable to the name of the
table(?) that contains the subform's data. Also change
combobox to the name of the combo box on the main form.

You will also have to add a line of code to the combo box's
AfterUpdate event procedure:
Me.[frm-Bid_Information-Subform].Form.Requery
and the same line would also have to go in the form's
Current event procedure.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top