Form Subform linking problem...

[CMooreJr]

Hey all...I have a form which includes a retailer address and a Vendor address, a week beginning field and week ending field,(as well as a Autonumber field that is the primary key)

I have a subform which shows the details of items the vendor has sold to the retailer for that week. Of course I need to link the 2 together, however there will be times when the date range will be the same for different vendors, the vendor will be for several retailers, etc...so I created a field labeled "SubformID" as a Number in the subform. I set the control source of the "subformID" to "=[Forms]![frmInventoryListing]![MyID]"
(The primary key)

I thought this would work, but when I go to enter data into the subform, I get an error which states that "Access cannot assign a value to this object. It may be read-only, in a form that is open in design view, or the value is to large for the field." Do you know what may be he problem??

Thanks!

 

[Rolliee]

sounds like you are trying to change an autonumber and that is a no-no

R

 

[sillysod]

i hate autonumbers with a passion, especially when someone balls's up an entry with a form, and it saves half a record because they close the form.

i just find them very restricting, since when you delete records you have to mess around compacting and repairing then appending the next number in sequence to the table so it doesnt skip from 100001 to 100025 after you delete records.

My advice, assuming you arnt going to have several thousands of records, is enter first two record numbers in excel eg EG1001 and EG1002 under it. then copy this down as far as you need, so say you have a range of EG1001 to EG2000 and copy and past this into access column.

this creates you 1000 ready to use records. and allows for a bit more freedom with prefixes etc (such as the EG prefix on the above example)

this has worked well for me, although it will mean your database is bigger to start with.

PS i know you can use the format to set autonumbers with a prefix eg "EG"0000 but it doesnt show the numbers correctly and stuff in my experience.

 

[CMooreJr]

yeah, I hate autonumbers to, but the data that resides in each form can be alike in almost every aspect but 1 or 2, so I need he subform to only match the one Unique field. That is why I was hoping that the "subformID" would work. It does put it in the subform correctly, and I'm not trying to alter that, just input data in the same record, but maybe it THINKS I want to change it....may just have to do what you are suggesting Silly...thanks for the input!

 

[kev747]

I had a little trouble understanding just how you want your form and subform to link together, so try this and see if it suits your needs.

1. Create an Autonumber in the main table (which you already have).
2. Create an autonumber in the subform table (which I assume you already have).
3. Create a field in the subform table called MainFormID.
4. Link the autonumber in the main form to the MainFormID field in the sub form (you do this in the form design view), and then make the MainFormID field invisible (purely for asthetic reason, of course).

This may fix your dilemma, if I understand you properly. If not, oh well, try something else.

Cheers,

Kev.


Never trust the French.
How can you trust a country that has 256 different types of cheese.
- Spike Milligan.