Error Messages On Subforms, Access 2016

[roxdrob]

Hi, I have a new database I'm trying to put together and have set up a couple of subforms that I'm having errors with. I've patterned them after another database that I have been working on that someone else set up a lot of it for me.



The first error I'm getting is "The object doesn't contain the Automation object "PaymentMethodID". This is in the top subform.

The second error I'm getting is "You tried to assign the Null value to a variable that is not a variant data type."

Anyone out there willing to help me with this, please?CraftShows.zip

[davegri]

What is the purpose of the database? Can you tell us in plain English what it is trying to accomplish?
Is this a db for craft show registrations? If so, where is the registrants table? I only see a table for the craft show itself.
Is the db just for a single Craft Show?

You have links in the Parent table pointing to the child tables. It should be the other way around. The parent table should typically not contain foreign keys.

[roxdrob]

Thanks for your response. It's a way for me to keep up with the shows I have joined. I need to know who the contacts are and what the hosts are expecting of me as well as how much I pay to attend. Does that make more sense?

[davegri]

Does that make more sense?

Yes, yes it does.
Here's an improved version to get you going:
davegri-CraftShows-v002.zip
This replaces the version below that I uploaded last night. Added referential integrity and cascade deletes (so as to not leave orphans when you delete a show).
Modified the Payment Method combobox dropdown source to be value list instead of listing previous entries in tblPaymentMethod.

Note: unable to find a way to delete attachment below, but that's the old file...

[roxdrob]

Thank you so much! This is wonderful!

[davegri]

Glad to help. Good luck with your shows!

[roxdrob]

I have a question. Why would the CraftShowID_FK go in tblNotes instead of a NotesID_FK going into the tblCraftShow?

[davegri]

Table craftshow is the parent. The parent can have many children. The children must point back to the parent, not the parent pointing to the children.
Think of it this way:
A show can have many notes. How could one show record point to more than one note?
But, any number of notes can point back to the show. Ah Ha!

[roxdrob]

Gotcha! Thank you.

[roxdrob]

I have come upon another problem:
I am trying to use a subform - "Specs" on my main form "CraftShows" which I set up as a combo box with choices like Electricity (provided), Juried, Donation (needed), etc. I set it up as a continuous form (subform). I am able to add one record, but get the below error when I try to add the second. Do I have some kind of parameter set wrong? Any ideas? (P.S. I know the form is ugly, I'm not done yet) The problem is on the bottom most subform. When I am adding a different spec.

[davegri]

All these fields are in tblCraftShows are they not? So you don't need a subform. The subform wants to add a new record which you don't need. They can go on the frmCraftShows just like webaddress and ContactEmail.
You can set up the combobox allowable values like I did with the Pymt Mthd combobox.

If you have added a new table to hold the spec, then the above is not valid. However, you would only need a new table and subform if a show could contain more than one spec.

[roxdrob]

Thanks for your response: ok, but how would I get it to list them separately. i.e., there are several options in the combobox. I would need to have more than one for each record. Is there another way to do that? Also, the Pymt Mthd combobox is in a subform as well and works fine.

[roxdrob]

Also, I may do the same show year after year and would like for the payment information to show up from year to year too. Does that make sense?

[davegri]

So you do have a new table, tblSpecs and a show could contain more than one set of specs?

[roxdrob]

yes, and yes.