select subform records to add to table and report

[socasteel21 via AccessMonster.com]

Help!!!

I started designing a database to allow the user to enter purchase orders.

Here's what I'm really trying to do:

I have a main form (frmPO) which is bound to a table (tblPO). This form just
includes miscellaneous customer information. It also includes a field called
"ModelNumber".

I have a subform (sbfrmOptions) which is bound to a query (qryOptions). The
list of options came from a table that was imported. I added a model number
field (the option list includes all available options for all models) and a
checkbox field to the table. When the user selects the model number in the
main form it filters the subform to contain only the options available for
that model. The user would then select (using the checkbox) which options
he/she would like. There is also a calculated currency field in the query
that basically says when the checkbox is checked to change the price from 0
in the calculated field to the actual price included in another column of the
query.

After selecting the options that the customer wants, I had a button that
would run 2 queries and open a report. The first query was an append query
that would append all selected options to a table called tblSelectedOptions.
It was supposed to include the PO ID number (autonumber field & pk). The
other query would clear out the subform (I did this because when I went to
another PO or record the options that I had selected in the previous PO were
still selected in the new one). Lastly, the button opened a report that
pulled the customer informations from tblPO along with each selected option
from tblSelectedOptions using the PO ID number.

I really don't think what I did was good design, but it was the only way I
knew to get it to work. The problem came when I tried to enter a second PO
in the database. The append query would add the newly selected options to
both POs that were in the database no matter the PO ID number.

If anyone could provide any help, it would be greatly appreciated. Thanks in
advance.



Shannan

 

[pietlinden]

Yuck. Personally, I think using checkboxes like this is a hideous
design. I almost never use checkboxes. If I do, it's for strict
Yes/No things. Why won't a subform with a combobox work? Then you
would need ZERO code to add stuff where you want it. then the report
is basically brain dead. If the main form and subform are linked,
going to a new main form record will force a blank subform record. No
brains, coding or anything else required. It's the way a normal
database should work.

Tell them if they want hideous workarounds, you'll charge them
triple... =)

 

[socasteel21 via AccessMonster.com]

I thought the subform and mainform were linked. I used the Model Number box
to filter the subform from the main form. I'm not exactly sure what you
meant by 'a subform with a combobox'. I need the user to be able to select
each option that he/she wants. I thought a checkbox would be the best way,
maybe not. Could you explain some more.

Thanks for your reply.

Shannan

 

[socasteel21 via AccessMonster.com]

I'm sorry if I wasn't clear before. I reread what I posted and found it to
be misleading.

I am using a combo box in the main form (cmbModelNumber) to filter the
records in the subform.

The checkbox field is on the subform.

After filtering the records, the user moves to the subform where he/she can
use the checkboxes to select which options he/she wants. I used an IIF
statement in a calculated field in the query that the subform is based on to
show 0 if the check box is not selected and the correct price if it is
selected.

Hope this clears up the confusion.


Shannan