Apply / link CustomerID from tblCustomer to tblQuotes

[valkyry]

I can't figure out why it won't apply the CustomerID to my Quotes.

I have a form for the main Customer information.
Then a button to open a form to enter quotes.

The Quotes table has QuoteID and CustomerID.

When the form loads from the Customer form and the quote information being entered, it automatically assigns the QuoteID and the data into the Quotes table.

But CustomerID is not linking.

I have a Subdataset view and it doesn't show a linked Quote.

HELP!

 

[ThomasLafferty]

Let's start with the obvious. Open your relationships window and confirm that there is a one to many relationship. The one side should be the Customer table; the many side should be the Quotes table.

If there is no relationship, drag and drop the CustomerID from the Customer table onto the CustomerID in the Quotes table. The Edit Relationship dialog should launch. Click Enforce referential integrity, cascade update, and cascade delete. The Relationship Type should say One-To-Many.

If that doesn't do it, open your Customer table, then click Insert>Subdatasheet. Confirm that the Link Child and Link Master fields match. (If your relationship is built properly, they should...)

Hope this helps.
Tom

Live once die twice; live twice die once.

 

[valkyry]

No, that is not the problem.
Or at least the relationship as such is been in place.

and yes on the subdatasheet.

 

[valkyry]

could it be because my CustomerID is a Text field and not an AutoNumber?
I don't see why this should be the case.

My main form is the Customers.
Then a button to enter into the Quotes form.

The Quotes from has a subform for Finishes.
These two link fine as it's linked off the QuoteID which is an AutoNumber field.

I can't get the CustomerID from the Customers to link to the Quotes.

The Quotes table has both QuoteID and CustomerID

 

[randy700]

I believe they have to be the same data type. Not autonumbers but text to text or numeric to numeric.


Randy

 

[valkyry]

they are. just wasn't sure if because it's a text-to-text was the issue.

any other ideas?
all of the above (obvious items) are not the issue.

I can't figure it out.

i'll copy and paste the exact info.

 

[valkyry]

this is on my Customer form on the Enter Quote button that opens the form to enter the quotes.




Private Sub EnterQuote_Click()
On Error GoTo Err_EnterQuote_Click
If IsNull(Me![CustomerID]) Then
MsgBox "Enter customer information before entering quote."
Else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.OpenForm "CustomQuotes", , , , acEdit
End If

Exit_EnterQuote_Click:
Exit Sub

Err_EnterQuote_Click:
MsgBox Err.Description
Resume Exit_EnterQuote_Click

End Sub

 

[valkyry]

the above is On Click.

then on the CustomQuotes form,
On Activate


Private Sub Form_Activate()
On Error GoTo Err_Form_Activate
Me.Requery
If IsLoaded("Customers") Then
If Forms![Customers]![QuoteDetails Subform].Form.RecordsetClone.RecordCount > 0 Then
DoCmd.GoToControl "QuoteID"
DoCmd.FindRecord Forms![Customers]![QuoteDetails Subform].Form![QuoteID]
End If
End If

Exit_Form_Activate:
Exit Sub

Err_Form_Activate:
MsgBox Err.Description
Resume Exit_Form_Activate
End Sub

 

[ThomasLafferty]

Hmmmm...
Here's a quick and dirty test to see if all is as it should be. Be sure that neither your Customer nor your Quotes form is open. Open your customer table. Click the autoform button on your Table Datasheet toolbar (the one with the lightning bolt.) You should get a form with your customer information as the main form, and a subform (datasheet style) to enter records in. Try entering a quote in the subform, close out (save changes to the form), open your customer table and expand the subdatasheet.

Tom

Live once die twice; live twice die once.

 

[valkyry]

ok,
i just added another quote for an existing customer and it did link it in the Table and can be seen in the subdatasheet.

 

[ThomasLafferty]

My bet then is a problem with your form design. Have you considered converting it to a main form/subform setup like what we just did? Why not use the subform wizard to embed the existing quote form in the customer form? You could show/hide the subform based on completion of a record in the main form... Just a thought.

Tom

Live once die twice; live twice die once.

 

[valkyry]

here are some key points.
1. Customer form - customer info, name, address and such.
2. subform on this form is just a list of all quotes against the customer.
3. double click on a quote to view the quote details.
4. So you have to click on Enter Quote from the Customer form to actually enter the quote details.
5. this is where I'm not getting the link from the Customer form (CustomerID) to write to the CustomQuote form.
6. in the CustomQuote form I have a subform that goes into another detail. Finishes. There's a one-to-many relationship from the CustomQuote to the Finishes.
7. from CustomQuote form, after the Finishes are entered, I have a button to preview the completed and faxable report which is triggered off of the QuoteID for the current customer record.

this is blank because the CustomerID is not writing / linking back to the CustomQuote to know which QuoteID belongs to the CustomerID.

 

[ThomasLafferty]

I see! Is it possible at this point:

5. this is where I'm not getting the link from the Customer form (CustomerID) to write to the CustomQuote form.

to embed this form into a main form with just CustomerID and perhaps CustomerName (or whatever you are using for that) in the header of the main form?) It sounds like your relationships are just fine...Tom

Live once die twice; live twice die once.

 

[ThomasLafferty]

You might also take a peek at this FAQ:
faq702-5860

Live once die twice; live twice die once.

 

[valkyry]

not sure what it was.
i deleted all my relationships and re-did them. and some other stuff so I can say exactly what did it
BUT it's fixed!!

thanks all!