Help needed with form (1 Viewer)

beanie_d83

Registered User.
Local time
Today, 11:59
Joined
Feb 5, 2013
Messages
29
Hi all,

I have a form which has a combo box to display a list of dealers which worked fine until I added a field from a different table. Now when I try and select from the combo box list it says join key is not in recordset.

Can anyone please explain what this means? Thanks : )
 

sneuberg

AWF VIP
Local time
Today, 04:59
Joined
Oct 17, 2014
Messages
3,506
Sounds like there's no join in the query for the row source of the combo box. If there's two tables in the row source query they should be joined.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,361
When you add a field from a different table, whether just on the form or specifically in the combo, you have to use a join between tables and modify your
-- form recordsource if field is on form generally, or
-- combo rowsource if field is part of combo.
 

beanie_d83

Registered User.
Local time
Today, 11:59
Joined
Feb 5, 2013
Messages
29
Hi there,

thank you both for your help on this.

When you say joined, do you mean by a relationship specifically like a 1-1 or 1-many or just a plain and simple joining line?

Form record source is tbl_BuildSheet (I think this is wrong also as I want the selected data to go here)

Row source for combo is tbl_Dealer (Lookup...not at table level)

Also, when I try to enter a record a message pops up telling me I cannot add or change a record because a related record is required in table tbl_Orders.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,361
Post a copy of your database in zip format.
Identify specifically the form involved and what you are trying to accomplish with the form and combo.
A sample with some data would be helpful.
 

beanie_d83

Registered User.
Local time
Today, 11:59
Joined
Feb 5, 2013
Messages
29
Ok here it is....albeit very basic

The form is called frm_BuildSheet

What I am trying to achieve is a form that allows you to build up the specification of a trailer. My first attempt I used up all the indexes because I had relationships linked to tables when all I really needed were lookups to tables.

The selection made in the combo box I want to be saved in tbl_BuildSheet under the field Dealer_Name.

The order number that is also entered in frm_BuildSheet needs to go into the Orders table (tbl_Orders).

In a nutshell this form is required to enter data into multiple tables.
 

Attachments

  • RWL_16DB(3).zip
    165 KB · Views: 145
Last edited:

sneuberg

AWF VIP
Local time
Today, 04:59
Joined
Oct 17, 2014
Messages
3,506
The relationships show that dealer is related to the order and not the build sheet so the dealer name shouldn't be in the tbl_BuildSheet. I suggest you make a form for tbl_Orders and put the the form for tbl_BuildSheet a subform of that form. The combo box for the dealer would be on the main form, i.e., the orders form.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,361
Further to sneuberg's comments, you should be using Dealer_Id to join Dealer and Buildsheet.
I'm not sure I understand the buildsheet and order, but my hunch is that in a form you should use a listbox to allow multiple selections and not a combo.

Please adjust this 30000 foot overview of the process.

A Dealer creates an Order, the Order gets broken down to a build sheet which identifies which parts and quantities of each are required to build the product. The underlying issue seems to be that a trailer is not simply an assembly of parts, but consists of basic parts and some sub assembly/configurations. So you may have a hierarchy involved???

We really need to understand the business processes and flow to get the tables and relationships designed and tested. It seems you may be a little bit too early to be involved with forms.

Just my $.02.
Good luck with your project.
 

beanie_d83

Registered User.
Local time
Today, 11:59
Joined
Feb 5, 2013
Messages
29
You have just about hit the nail on the head there so closer than you think!

We receive orders from the dealerships that sell our machines around the country and occasionally for export. The order will naturally have an order number for the different machines they want (one, multiple or various). As you might expect when purchasing a motor vehicle there are various options available for the build spec and this is what I am imagining the so called BuildSheet form will facilitate. I am not wanting to break it down into individual components just yet as I feel I am already attempting the impossible with my Access know how! However, it would be nice to be able to store component serial codes such as axles just in case there are any faults with them.

I will make tables for all the available options (some options will depend on other choices so filtering out by selection will be needed) and use the combo boxes to look them up. All this data will then be stored and used to produce a build sheet/report that stays with machine throughout its production process so the fabricators know what to fit.

Once finished, it will then be scheduled for delivery (or collection) and we require the dealers to fill out a PDI/Registration form so we have a record of the customer who has purchased it. This makes sure that the trailer meets the order and customer satisfaction. It also will help me in keeping track of warranty claims/service visits as sometimes things do go wrong.

Sorry for the lengthy essay, I hope this shines some more light on what I am trying to achieve?
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,361
Hmmm... not a strategy I'd recommend. I suggest you don't start programming and dealing with combos before you have some of the basic business facts organized and tested. ( a basic data model).
Since you have sub-assemblies (axles) and you need to record serial numbers (component serial codes), then you have a hierarchy.
See this for more info.

Good luck.
 

beanie_d83

Registered User.
Local time
Today, 11:59
Joined
Feb 5, 2013
Messages
29
The relationships show that dealer is related to the order and not the build sheet so the dealer name shouldn't be in the tbl_BuildSheet. I suggest you make a form for tbl_Orders and put the the form for tbl_BuildSheet a subform of that form. The combo box for the dealer would be on the main form, i.e., the orders form.

So by having the order linked to the BuildSheet doesn't mean Access can link it back to the dealer then? I will check what I have done when at work tomorrow but I must be misunderstanding the way I need to link the tables.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,361
The relationships are determined by your business rules.

What does the Dealer do, exactly?
 

beanie_d83

Registered User.
Local time
Today, 11:59
Joined
Feb 5, 2013
Messages
29
Hmmm... not a strategy I'd recommend. I suggest you don't start programming and dealing with combos before you have some of the basic business facts organized and tested. ( a basic data model).
Since you have sub-assemblies (axles) and you need to record serial numbers (component serial codes), then you have a hierarchy.
See this for more info.

Good luck.

Would it simplify things a bit if the axles are a bought in component from a supplier? I don't need to break these down into their individual parts but the axle as a whole has a serial code which I want to record so it can be traced to a particular machine.

In short, does this still mean hierarchy is still involved?
 

beanie_d83

Registered User.
Local time
Today, 11:59
Joined
Feb 5, 2013
Messages
29
The relationships are determined by your business rules.

What does the Dealer do, exactly?

The dealers are different agricultural stores across the country who sell our machines to farmers. They also carry out repairs under warranty on our behalf which we reimburse them for
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,361
OK good info, but I'm interested in what the Dealer does with respect to an Order.
What is it that the Dealer does with a Buildsheet?
The general concept here is who does what with what, and in what sequence?
 

beanie_d83

Registered User.
Local time
Today, 11:59
Joined
Feb 5, 2013
Messages
29
Well the dealer places an order with us for their customer (this can just as easily be a stock order for the dealer to sell as and when required or for a hire fleet during the harvest season) and comes in as an itemised list with all the requested options, quantities, etc.

The dealer has no interaction with the buildsheet in any way. This is something we use internally (an Excel template at present which is manually typed out) to transfer the order into a specification sheet that the fabricators can easily and quickly interpret so they know what to make.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,361
OK. You are familiar with the process and jargon and that is good. For the readers (the older/slower ones like me) it is trying to get past the jargon and to the essentials. Let me try to describe things as I understand them at the moment.

Your company manufactures Trailers.
Your company has a number of Dealers.
A Customer orders/buys one or more Trailers from a Dealer
An Order identifies the Trailer model/configuration with options/customization.
When the Order reaches your facility it is broken down/mapped to a Build sheet (similar to a pick list)
The Build sheet lists all the parts and components required for the Trailer for the Fabrication shop
Some Parts are purchased from/supplied by External Supplier
Some Parts are sub-Assemblies/Components that are uniquely identified (serial number)

Is there a list of Options that can be selected for various Trailer models?
 

beanie_d83

Registered User.
Local time
Today, 11:59
Joined
Feb 5, 2013
Messages
29
Well in most cases an order can identify a trailer/machine, however from time to time we build trailers for our own stock or as a prototype and so would not have an order number in these instances.

It is the trailer Production Number that we use to identify the machines, much like a VIN number on car.

When the order is received, the trailer model and any of the optional extras are listed on the build sheet and not the individual components as the BOM is taken care of by the CAD software.

There is an options list for the various types of machines we build but depending on the type of machine these options can differ (for example, we build grain trailers and manure spreaders but not all optional extras are available or relevant to both).

Would tables used as lookups containing the different options be the answer here?
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,361
Here's my latest revised list of facts based on your posts:

Our company manufactures Trailers/Machines.
Trailers/Machines can be of different types.(grain trailer, manure spreader...)
Our company has a number of Dealers.
A Customer orders/buys one or more Trailers/Machines from a Dealer
An Order identifies the Trailer model/configuration with options/customization.
We also build trailers/machines for our own stock and as prototypes. These builds do not have an order number.
When the Order reaches our facility it is broken down/mapped to a Build sheet (similar to a pick list)
The Build sheet lists all the Trailer/Machine Models and their Options
The Build sheet identifies all of the parts and components for the Trailer/Machine
The Fabrication shop builds the Trailer/Machine baseon the Build sheet.
The Production Number (VIN alias) is assigned as the Build sheet is populated.
Some Parts are purchased from/supplied by External Supplier
Some Parts are sub-Assemblies/Components that are uniquely identified (serial number)
 

beanie_d83

Registered User.
Local time
Today, 11:59
Joined
Feb 5, 2013
Messages
29
Our company manufactures Trailers/Machines.
Trailers/Machines can be of different types.(grain trailer, manure spreader...)
Tailers/Machines have different models (mainly by carrying capacity)
Our company has a number of Dealers.
A Customer orders/buys one or more Trailers/Machines from a Dealer
An Order identifies the Trailer model/configuration with options/customization.
We also build trailers/machines for our own stock and as prototypes. These builds do not have an order number.
When the Order reaches our facility it is broken down/mapped to a Build sheet (similar to a pick list)
The Build sheet lists all the Trailer/Machine Models and their Options
**The Build sheet identifies all of the parts and components for the Trailer/Machine**
The Fabrication shop builds the Trailer/Machine based on the Build sheet.
The Production Number (VIN alias) is assigned as the Build sheet is populated.
Some Parts are purchased from/supplied by External Supplier
Some Parts are sub-Assemblies/Components that are uniquely identified (serial number)

**The build sheet doesn't go as far as breaking the machines down to their individual components, merely the options that have been chosen by the customer** It would be useful perhaps at a later date to have this but from what you have previously advised this will involve a complicated hierarchy database?

Other that that I think the above is as accurate as it can get for the moment.
 

Users who are viewing this thread

Top Bottom