Unwanted Enter Parameter Value Box

[Linda1503]

Delete the combo, save the form, compact, add the combo back

Thanks Pat - black magic But I think my problems are more fundamental
Re your earlier answer I started without the junction table but ended up with all the same errors - this is just the last of many ineffecual attempts to make it work... failing a solution I think I'll finally cave in and add the types to the start of the product names :-/// eeeuck!

 

[Pat Hartman]

When I create combos, I frequently concatenate two columns for display purposes. When a combo's list is closed, you can see only the first column. This isn't always convenient for the users. Do NOT alter the text names permanently, just concatenate them on the fly.. That allows you to keep a properly normalized schema but use a single combo.

Select PK, fld1 & " " & fld2 As Display
From YourTable
Order By fld1 & " " & fld2

Here's a sample that shows how cascading combos work:

 

[Linda1503]

Thanks Pat, I'd figured out the concatenation thing to get the type & sub types to show together but am lost in a morass of failed attempts on the dropdowns - I'll have a look at file you sent - Thank you!
Any chance you could have a look at my relationships to see if I've done something stupid - logically the product types should be linked to the products rather than the order details, but that seems??? to be causing problems Arrgh - could be any one of a thousand things seems to me.
Tried changing the relationships but ended with similar problems :-/ Currently loosing the will to live & available time to work on it...
Sorry for taking up your time - am very grateful!!!

Current relationships:

Different approach (that didn't work :

 

[Linda1503]

Wasn't getting anywhere with it so copped out and used a single form with a subform below showing the whole order - still have to test n tweak but it does seem to be reliably functioning - thank goodness!!!
Many thanks for all your input Pat
Lx

 

[Pat Hartman]

The second diagram creates a circular relationship and I would recommend against that. It doesn't make sense that both the parent and the child tables would have a 1-m relationship with the same table. If the type is the same for all items on an order, then the FK belongs in the parent table. If it can be different for different detail rows, then there is no way there can ba only a single value in the parent record.

I would use two tables rather than three.

T1:
TypeID (autonumber PK)
Type (unique index to ensure no duplicates)

TSub:
SubID (autonumber, PK)
TypeID (FK to T1)
SubType

In Product, you would keep ONLY SubID. The RowSource query for the combo would be

Select SubID, Type & " - " & SubType As FullType
From TSub Inner Join T1 On TSub.TypeID = T1.TypeID
Order By Type & " - " & SubType

 

[Linda1503]

Sorry for delayed response - had to go fit shelves for son's new flat...
On the circular relationship in second diagram - don't truly understand - but you're right, it doesn't work.
On the number of tables needed I've now managed to convince myself (probably wrongly ) that I do need three...
Pork (T1) can be rolled or made into steaks (SubType)
Beef (T1) can be rolled or made into steaks (SubType)
surely that's a many to many relationship so would need a third table?

 

[The_Doc_Man]

No, not a third table. If the only difference is beef or pork, have a field in the table that says what it is. "Beef" "Pork" "Chicken" "Fish" "Alligator" (Sorry, my south Louisiana roots are showing ...)

 

[Linda1503]

Thanks Doc Man, but there are (so far) 39 Types and 88 SubTypes and 129 combinations so...

 

[The_Doc_Man]

The issue of type/subtype can be handled several different ways, at least one of which is that the type and subtype are two fields in the same table.

 

[Pat Hartman]

If subtype stands alone, then it isn't subordinate to type and if you want to relate many types with many othertypes, then yes there is a m-m relationship. But "subtype" would be assumed to belong to type and therefore NOT stand alone. For example, there are many cities in the US named Lincoln. is one Lincoln the same a another? NO, each Lincoln is different and exists in a different state. So State is "type" and city is "subtype". They have a 1-m relationship, not a m-m. We have a State (type) table. It has 51 entries (50 states plus DC or more entries if it includes dependencies like Puerto Rico). In the City table (subtype) there is a Foreign Key that points to the State in which the city exists.

Don't be confused by the subtypes having the same name.

 

[Linda1503]

If subtype stands alone, then it isn't subordinate to type and if you want to relate many types with many othertypes, then yes there is a m-m relationship. But "subtype" would be assumed to belong to type and therefore NOT stand alone. For example, there are many cities in the US named Lincoln. is one Lincoln the same a another? NO, each Lincoln is different and exists in a different state. So State is "type" and city is "subtype". They have a 1-m relationship, not a m-m. We have a State (type) table. It has 51 entries (50 states plus DC or more entries if it includes dependencies like Puerto Rico). In the City table (subtype) there is a Foreign Key that points to the State in which the city exists.

Don't be confused by the subtypes having the same name.

Thanks Pat, very clear but in this instance the type is usually some sort of product description (beef, pork, pies, salads) and the sub type the preparation description - which can be applied to many different product types (although occasionally reversed eg cooked meats as type with beef, pork etc as sub).

 

[Linda1503]

Thanks Pat, very clear but in this instance the type is usually some sort of product description (beef, pork, pies, salads) and the sub type the preparation description - which can be applied to many different product types (although occasionally reversed eg cooked meats as type with beef, pork etc as sub).

Sorry - inadequate info from my end causing confusion

 

[Linda1503]

& crap naming of tables!

 

[Linda1503]

Maybe I should post this bit as a new question but...
This DB is used to gather Christmas order prep info & generate production crosstabs, route planning uploads, delivery schedules, labels etc for when there's a huge amount of product (for a small business) going out over a couple of days (mayhem!!!). Historically I've done the data entry & reports. But it's not good for a business to be so dependant on 1 person - they can't afford a 3rd party to design an app (& I'm too fearty to try ).
Question: Is there a way to share an order recording form from the DB on a network (no idea how to set that up yet) between the office windows pc and a tablet (they have an IPad) in the shop?
I'm a total novice on that sort of thing :-/ so any help much appreciated!!!

 

[isladogs]

I'm too fearty to try.

Fearty????

 

[The_Doc_Man]

The only way that you can use a remote connection like that involves some type of
(a) split database with a non-access backend and (a.1) Access front end to SQL backend and (a.2) separate web front-end to the SQL, or
(b) some sort of Remote Desktop Protocol so that the table "logs in" an RDP session that can touch Access.

The problem with using Access over any kind of wireless network is that one momentary network drop and your back-end is potentially toast. The Access File Sharing protocol (actually called SMB or Server Message Block) does not play nicely with wireless networks. Not nicely at all. Very likely to corrupt the back-end if it is native Access.

 

[Linda1503]

Fearty????

You get the gist I'm Scottish!

 

[Linda1503]

The only way that you can use a remote connection like that involves some type of
(a) split database with a non-access backend and (a.1) Access front end to SQL backend and (a.2) separate web front-end to the SQL, or
(b) some sort of Remote Desktop Protocol so that the table "logs in" an RDP session that can touch Access.

The problem with using Access over any kind of wireless network is that one momentary network drop and your back-end is potentially toast. The Access File Sharing protocol (actually called SMB or Server Message Block) does not play nicely with wireless networks. Not nicely at all. Very likely to corrupt the back-end if it is native Access.

The only way that you can use a remote connection like that involves some type of
(a) split database with a non-access backend and (a.1) Access front end to SQL backend and (a.2) separate web front-end to the SQL, or
(b) some sort of Remote Desktop Protocol so that the table "logs in" an RDP session that can touch Access.

The problem with using Access over any kind of wireless network is that one momentary network drop and your back-end is potentially toast. The Access File Sharing protocol (actually called SMB or Server Message Block) does not play nicely with wireless networks. Not nicely at all. Very likely to corrupt the back-end if it is native Access.

I could maybe copy the DB onto a laptop and set up an export file to email/google docs that could be appended to the main DB? Might have to brush up on appending data or would a union query be less fraught?

 

[isladogs]

You get the gist I'm Scottish!

I did indeed get the idea. As in

The Prime Minister should stop being a big fearty and come to Scotland to debate with me. (Alex Salmond)

 

[Pat Hartman]

iPads are pretty useless in a Windows environment. a Surface pro would be significantly more useful

& crap naming of tables!

Yep.

The Type and SubType tables have no relationship to each other but they each have a relationship to the product. And please do find better names for them.