Form Query / Grouping issue

[nicksource]

I have a form that shows what products go out of the store and to what person.

The form creates a row for each product to that one person in the table.

To group this data, I want to assign some sort of number that is inserted into each row, so I know that each of these products was sent out together. As each row for that person will have for example 1003 next to it... then the next group of products will all have 1004.

What I have done is created a table that stores a number, then a SQL query to get the last number... I was then thinking of just adding +1 to the end of this query (to get the next number)... but when I try to include the query into my form I get a #name? error, even thought it works fine without.

-----

My SQL Query:

SELECT TOP 1 GoodsOutNo.GoodsOutNo
FROM GoodsOutNo
ORDER BY GoodsOutNo.GoodsOutNo DESC;

GoodsOutNo table:

ID | GoodsOutNo
1 | 1000
2 | 1001
3 | 1002
4 | 1003

So query says '1003'.

In my form field, I have this to ask for the query (but get the #name? error)

[GoodsOutNoSelect]![GoodsOutNo]

Thanks in advance! Help will be very appreciated.

 

[stopher]

A form is usually based on a single data source (either a query or a table). This is specified in the forms properties. Then the elements (objects) of your form are based on (bound to) the fields in your data source. This is done in the properties for each object.

So I would expect the data source for your form to be GoodsOutNo and the field in your form object to be [GoodsOutNoSelect].

However, it looks to me like you are trying to set the data source of one object to a specific data source/field combination. You can't do this. If you need data from another data source other than the one defined in the forms data source then either ....add create a join query with all the required data you need .... or use the DLOOKUP function.

hth
Chris

 

[nicksource]

How would a DLookup function do the job?

I can add the query as a subform or create a form specifically for that query (but then can't use other table fields in it).

What would you suggest to setup a system like this?

Thanks.

 

[stopher]

Perhaps you could explain more about what it is you are trying to do rather than me giving you a solution that might not be appropriate.

What is your data source for your form?

What is this GoodsOutNo in relation to the rest of the data on the form?

Are you using subforms? Are you trying to reference data in subforms?

Chris

 

[nicksource]

Perhaps you could explain more about what it is you are trying to do rather than me giving you a solution that might not be appropriate.

What is your data source for your form?

What is this GoodsOutNo in relation to the rest of the data on the form?

Are you using subforms? Are you trying to reference data in subforms?

Chris

This will explain it a little better to what I want to achieve:

As you can see, I have a form that selects multiple products (from a 'products' table) then inserts it into the 'GoodsOut' table, but I have no idea of knowing which products were together in the same form, so I can't organize or group the data.

It doesn't have to be a separate table for the GoodsOutNo, even if I could get it to all have the same ID such as 22 in each row under the GoodsOutNo, just I don't know how to echo it (if I do '=[ID]' it shows it but doesn't record it to the table).


Here is a picture of my form:

 

[stopher]

Thanks for the diagrams. I think I know what you are asking (but not sure).

I think you have set up a form and subform. The form is an order and in the order you have a subform showing all the items on the order. But your problem is you don't have any reference between your form and subform. Is that correct?

First of all, have you normalised your tables and related them? ie. Do you have primary keys in your tables. And then do you have a foreign key in the table for your goods out subform? This will be the key to use in linking the form and subform.

To link the form and subform so that the key is in both, probably the easiest way is to remove the subform then re-add it using wizard. When you use the wizard it will ask you which fields to link the form and subform by. Once you have doen this then when you add records to your subform, the key field for the subform will automatically be completed according to what it is in the main form. Thus this key will be available for reporting, aggregating etc.

hth
Chris

 

[nicksource]

Ahhh, thank-you so much, it worked, huge relief!

I just have one more little issue though, here is a picture:

As you can see each product row inserted the original ID number (37 in this case) in successfully thanks to linking the subform, just how do I insert the same ID into the first row too? It seems if I try and set a default value or '=[ID]' in the GoodsOutNo it doesn't work.

Thanks alot Chris.