Table setup for use in Form with subform 2

[Sloaner]

I've created a form that contains a subform with Order details info. The main form MainSys has a primary key OrderID and the subform SysDetail has a primary key OrderDetailID and also contains OrderID. The link Child fields and link Master fields are OrderID & OrderID. The subform's recordsource is a table name Orderdetails.
I now have to create another form to show whether or not the orders were shipped but using the Recipient's name as a link rather than the OrderID. I need to know how to Choose the Link Child Fields and Link Master Fields to get the details for the orders to show per person rather than per order... ie. the details for the new form should show all the orders placed by Customer John Smith as.

John Smith
ABC CO
111 smallTown USA

Item# Description Qty Shipped Balance
1 Boxes 10 Y 0
2 Bottle 5 N 5
3 Folders 12 Y 6
4 etc....

Can someone help with this...
It somehow seems simpler to have Order and details linked by OrderID but to now have those same details linked to a person's name throws me off a bit. I read up a bit on this but still needs help to clearly understand how to build these.

Thanks in advance.

 

[KenReay]

Hi

Make a query joining Order Header Table and Order Items table on OrderId. AS output columns, include all of the columns in the Items table, plus the Recipient Name from the Order Header

Make a form based on this query

Place the subform control on the main form, setting master/child of the subform control to Recipient name

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[willir]

Sloaner

This should not be hard. You have done a great job describing invoice / invoice detail. Great stuff.

The part of the design you are moving to is the Customer table.

CustomerTb
CustomerCode - primary key, text (or CustomerID, autonumber)
CustomerName
+ other stuff regarding the customer

Then, on your invoice table, you call it MainSys

MainSys
OrderID - primary key
CustomerCode - foreign key to customer table
+ other invoice table

Notice that the Invoice (MainSys) / InvoiceDetail (SysDetail) tables has a one-to-many relationship.

It is the same for the customer. One customer can have many invoices. (Some more than we want ;-) )

It is the same thing except you are going from the customer table to the invoice table, instead of from the invoice table to the invoice detail table.

Richard

 

[Sloaner]

Thank you all for responding.
Ken,
I tried your approach...but am having some problems...
hope you're still available to help.
First I have to tell you that I have created the form with a combobox that select the customer name from the MainSys table and fills in company and location. when I built the query as you suggested and added the subform it displayed the name selected with the company and location but also displayed all the orders that are in the table so far. I believe that's because RecipientName is not in the SysDetail table... and I know you said not to add it.
Any suggestions?

 

[Sloaner]

Richard,

I understand what you've proposed but what I'm looking for is actually for Customer and Invoice Details.In your scenario a customer can have many invoices. What I'm looking for is the details on all the invoices per customer. The problem is that MainSys doesn’t have the info that I’m looking for, SysDetail does.
MainSys will give me things like billing address,PO num etc… nothing to do with the actual products ordered.
Also the Key is not OrderID in SysDetail it’s OrderDetailID. Eventhough it does contain OrderID, in my first form OrderID was the primary key in MainSys and the foreign key in Sysdetail.
Also in reference to your CustomerTb, I already have a Contact table with Name and Address which I used to create the recipient field in the first form (MainSys). So the MainSys has the Recipient Name that I’m looking for. I understand now that it was probably best to keep the CustomerID rather than the Concatenated name but since I already have it I was going to use that in my second form.
In all, I’m looking to search the invoice MainSys table for a name and then bring up all the orders details that are associated with that name(which is the info in the subform running off of SysDetails).

What I would like to have is a combobox that pickups the Recipient name from the MainSys table and fills in the Company , Location associated with that person and all the orders (i.e order details) that should be shipped to them.
The purpose for this is for a user to be able to lookup a customer’s different orders by name and be able to tell them whether or not the order(s) have been shipped fully/partially among other things…
I hope this is clearer and you're still available to help me thru this. If I understood your setup wrong … let me know..

Thanks in advance.

 

[KenReay]

Hi

Have you set the master/child properties of the sub form control to Recipient Name as I said?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Sloaner]

Yes I did.. I just double checked. The master/Child properties are both set to Recipient.

 

[KenReay]

Hi

In that case it should be showing teh Order Deatils ONLY for the Recipient name which is selected in the main form

Is it?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Sloaner]

No it's not ... it's showing all the order details that exist. You see so far I only have a small sample in the DB as I'm still building it. So I know that there are 4 orders in the DB and the Details total 8. When I bring up the form I see 8 orders and 8 details on every single record.Some of the Recipients are doubled up in the combobox.
I just thought of something... when you said

Make a query joining Order Header Table and Order Items table on OrderId

Did you mean the query should be from MainSys and the Sysdetails? or from Company, Location and the SysDetails.
Right now I have it on the MainSys & SysDetails and I think that is why the combobox shows the name multiple times which would corresponds to the multiple orders in the MainSys table.
Let me know

Thanks

 

[KenReay]

The query I suggested you should make was to be the source of the subform, NOT the combo box, the combo Box should be based on whatever table has the recipients names in it (Customer table perhaps)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[willir]

Hi Sloaner

I realize you are looking for the detail information. I please understand that I have to improvise for the "gaps" which is why I depicted a customer table - your contact table is perfect / same concept.

If Ken's suggest works - great! If not, here is another approach.

Your MainSys provides the pivotal roll.

[tt]
Contact -------------> MainSys ----------> SysDetails
ContactName (?) -> RecpientName (?)
OrderID -------> OrderID
[/tt]

I am not sure how you linked the Contact table to your MainSys table. You indicated to Ken that you use ReceiptName. This is a little risky if there is a mis-match in spelling

You can use the query builder to join the three tables. You need to include the Contact.ReceipientName and all of the SysDetail table.

Your subform is built from this SQL statement.

Overview on how it works:
- Build an SQL statement based on the current record.
- Use the SQL query to repopulate the subform

Assumptions:
- ReceiptName is a text string field that is the primary key or has a unique index in your contact table, and is a foreign key in the SysMain table.
- InvoiceID is a numeric (long interger) field that is the primary key for your SysMain table, and is a foreign key for your SysDetails table.

The SQL statement I came up for you is...
[tt]
SELECT C.RecipientName, D.*
FROM (Contact as C
INNER JOIN SysMain as S ON C.RecipientName = S.RecipientName)
INNER JOIN SysDetail as D ON S.InvoiceID = D.InvoiceID;[/tt]

You may have to tweak this a bit - for example, is it SysDetail or SysDetails, ReceiptName or Receipt_Name?

Okay, now for some coding examples...

Assumptions:
Name of fields on form are...
Me.RecpientName
Me.InvoiceID

Code based on form with Recipient info on main form

Code of AfterUpdate field event

'For the combo box used to select Recipient
'Name of combo box assumed to be named RecpientName

If Len(Nz(Me.RecpientName, "")) > 0 Then
     'Call module to do work
     FindDetails
End If
[code]

Code for OnCurrent record
[code]
'For the current record, if recipent info selected
'Name of combo box assumed to be named RecpientName

If Len(Nz(Me.RecpientName, "")) > 0 Then
     'Call module to do work
     FindDetails
End If
[code]

Okay, no for the FindDetails module
[code]
Private Sub FindDetails()

Dim strSQL as String, strQ as String

strQ = CHR$(34)

StrSQL = "SELECT C.RecipientName, D.* " _
& "FROM (Contact as C " _
& "INNER JOIN SysMain as S ON C.RecipientName = S.RecipientName) " _
& "INNER JOIN SysDetail as D ON S.InvoiceID = D.InvoiceID " _
& "WHERE C.RecipientName = " & strQ & Me.RecpientName & strQ

'Now the SQL statement has been created with Receipent name
Me.NameOfYourSubForm.Form.RecordSource = strSQL1
Me.NameOfYourSubForm.Requery

End Sub

This same code would work for the InvoiceID by just changing the WHERE clause
[tt]
& "WHERE D.InvoiceID = " & Me.InvoiceID[/tt]

Got to run...
Hope this explained things better, and is do-able for you...
Richard

 

[Sloaner]

Richard,

I'm in the process of implementing your code and was wandering if you were available today whether or not you could take a look at the code you provided to see if may be there was a piece missing. First I have to say that I decided to go back and change the layout so Recipient actually saves the person's ID instead of the Name eventhough the user sees a name on the screen. That being said I have PersonID as the key to Contact and that is linked to the Main table(ORDER) with Recipient as the foreign key. The relationship is 1 to many between Contact and Order.
I'm by no means an expert and cannot figure out why I get an error in this code for the SQL.

SELECT C.RecipientName, D.*
FROM (Contact as C 
INNER JOIN SysMain as S ON C.RecipientName = S.RecipientName) 
INNER JOIN SysDetail as D ON S.InvoiceID = D.InvoiceID;

I did substitute the field names to match exactly what I have

SELECT C.Recipient, D.*
FROM (Contact as C 
INNER JOIN Order as S ON C.PersonID = S.Recipient) 
INNER JOIN Printjobs  as D ON S.OrderID = D.OrderID;

and get the following error.
"Syntax error in FROM Clause"
and the cursor comes back with the table ORDER highlited.
The contact table has PersonID as primary key, Order has Recipient as Foreign Key.Order has OrderID as primary key and Printjobs has OrderID as Foreign Key.
The table name is spelled correctly ... I cannot think of what else this could be. Access is not allowing me to save the query as a result.
Also what are the effect on the code above that my changes will cause.Recipient is a number now that saves the actual ID as opposed to text that used to save the Name.

Suggestions??? I cannot move on to building the subform.

Help!
:-(

 

[Sloaner]

Never mind my previous post ... I got it to work.

Thanks for your suggestions to you both Richard and Ken.
This was a good opportunity to try different things and learn.

 

[willir]

Sorry, I was out for the past two days... Glad you got things working, but I am sure it requried a bit of elbow grease... Well done.