Combo Box works for New Records but not for Record Updates (1 Viewer)

PuzzledNH

Registered User.
Local time
Today, 04:50
Joined
Nov 4, 2019
Messages
36
I am trying to develop a form that allows me to enter shipment tracking info (Fed-EX # and ship date) into a SHIPMENT table, select the customer, contract, and delivery address via a dropdown from a CUSTOMER table, and select one or more serial numbers from a PRODUCTDATA table to create a shipment record.

Since there is one customer to a shipment, I was able to set up a combo box on the form and select the Customer & contract info from the CUSTOMER table. This displays the customer, contract, and delivery address fields and saves the CustID PK from the CUSTOMER table in the CustID FK of the SHIPMENT Table. This seems to work OK when form is open to add New Record. However, If I open an existing record, I receive popups to enter parameter values rather than having the option to select Customer info from the combo box.
Note: A copy of the relationships diagram is attached in case I messed up on a link.

Any suggestions on how to set this up are appreciated.
Thank you.
 

Attachments

  • RelationshipCapture.JPG
    RelationshipCapture.JPG
    46.4 KB · Views: 104

theDBguy

I’m here to help
Staff member
Local time
Today, 01:50
Joined
Oct 29, 2018
Messages
21,357
Hi. I think we'll need to see how you have set up the Combobox. For example, what Row Source are you using?
 

plog

Banishment Pending
Local time
Today, 03:50
Joined
May 11, 2011
Messages
11,611
I made 2 points in your prior thread (https://www.access-programmers.co.uk/forums/showthread.php?t=307809) that you seem to be disregarding. I'm not certain if that was intentional or not, so I will just post this last time and be out of your way:

1. Tables, then reports/queries then forms last. Seems you've decided to touch on tables then head straight to forms.

2. Your tables still aren't structured properly nor are your relationships correct. As stated in the prior post PRS11-820228 isn't a field name, it should be data in a field. Also, in the screenshot you posted in this thread your relationships create a loop (tblProductData is related to tblCustomer is related to tblShipment is related to tblProductData). That's not how relationships should work--there should only be one way to get from one table to another. I don't know what the right answer is, but I do know your relationships are incorrect.

I suggest that you work on your tables before moving on--but I am no longer going to hound you about it. Simply don't reply to this post and I will be out of your hair.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:50
Joined
Feb 19, 2002
Messages
42,970
I'm with plog on this. Our comments are not differences of opinion. They go to the underlying soundness or more correctly, the lack there of, for your initial schema.

Start by removing Customer_ID from tblProductData. It only belongs in tblShipment and as plog said, you still have column names that are actually data.

And finally, and this is an opinion rather than a fact, using different names for PK/FK pairs only leads to confusion. If you call the PK ProdID, calling the FK ProductLine_ID simply adds complexity and confusion. It is not helpful at all. Nor is there any reason to do this. If you create a query that joins these two tables, you would only ever select ProdID from tblProductData (i.e. the FK). You would never select ProdID from tblProductLine (the PK).

If you really want differences between PK and FK names, then suffix the FK with "_FK". So the PK is ProdID and the FK is ProdID_FK. At least that makes sense and you will easily remember it as long as you name ALL FK's this way. Consistency is your friend.
 

PuzzledNH

Registered User.
Local time
Today, 04:50
Joined
Nov 4, 2019
Messages
36
I made 2 points in your prior thread (https://www.access-programmers.co.uk/forums/showthread.php?t=307809) that you seem to be disregarding. I'm not certain if that was intentional or not, so I will just post this last time and be out of your way:

Hi plog,
Not intentional. Just my ignorance showing through.I havent worked on a database since I took a class 10 years ago. I am definately here to learn and am just struggling with how to break down the data tables.


1. Tables, then reports/queries then forms last. Seems you've decided to touch on tables then head straight to forms.

I did listen. :) I wanted to verify the data did not get distorted during restructuring of the report. Thus, I developed a couple of typical queries and reports from the new tables and they "appear" to be working properly. A copy of one of the formatted reports is attached.

2. Your tables still aren't structured properly nor are your relationships correct.

Based on the struggles I am having with setting this up I can fully agree with this statement.


As stated in the prior post PRS11-820228 isn't a field name, it should be data in a field.

I will revisit this in my prior thread on the Tables forum with a new post and more details.



Also, in the screenshot you posted in this thread your relationships create a loop (tblProductData is related to tblCustomer is related to tblShipment is related to tblProductData). That's not how relationships should work--there should only be one way to get from one table to another.I don't know what the right answer is, but I do know your relationships are incorrect.

Sheepishly bangs head on wall. Thank you for pointing this out. I am attempting to fix this and see if it changes anything.


I suggest that you work on your tables before moving on--but I am no longer going to hound you about it. Simply don't reply to this post and I will be out of your hair.

Please don't think you are hounding me. I am here to learn and need someone to tug on my hair and kick me in the right direction. :) I sincerely thank you for the help.
 

Attachments

  • 60CM Inspection Report.pdf
    55 KB · Views: 121

PuzzledNH

Registered User.
Local time
Today, 04:50
Joined
Nov 4, 2019
Messages
36
Start by removing Customer_ID from tblProductData. It only belongs in tblShipment and as plog said, you still have column names that are actually data. And finally, and this is an opinion rather than a fact, using different names for PK/FK pairs only leads to confusion. If you call the PK ProdID, calling the FK ProductLine_ID simply adds complexity and confusion. It is not helpful at all. Nor is there any reason to do this. If you create a query that joins these two tables, you would only ever select ProdID from tblProductData (i.e. the FK). You would never select ProdID from tblProductLine (the PK).

Hi Pat,
Based on plog and your input, I redid the relationships and re-labeled the relationship pairs to be the same. See the attachment for the new relationships table. Thank you.

I am still working/struggling on column names and will repost in my prior thread on the Tables forum.
Again, I sincerely appreciate all the help.
 

Attachments

  • RelationshipCapture2.JPG
    RelationshipCapture2.JPG
    42.3 KB · Views: 78

PuzzledNH

Registered User.
Local time
Today, 04:50
Joined
Nov 4, 2019
Messages
36
Hi. I think we'll need to see how you have set up the Combobox. For example, what Row Source are you using?

Hi theDBguy,

Control Source:
CustID​
Row Source:
SELECT [Customer].[CustID], [Customer].[Customer], [Customer].[Contract], [Customer].[DeliveryOrder], [Customer].[CLIN], [Customer].[DeliveryLocation] FROM tblCustomer;​
Row Source Type:
Table/Query​

Thank you.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:50
Joined
Oct 29, 2018
Messages
21,357
Hi theDBguy,

Control Source:
CustID​
Row Source:
SELECT [Customer].[CustID], [Customer].[Customer], [Customer].[Contract], [Customer].[DeliveryOrder], [Customer].[CLIN], [Customer].[DeliveryLocation] FROM tblCustomer;​
Row Source Type:
Table/Query​
Thank you.
Hi. Thanks. But unfortunately, that doesn't reveal anything that could point into the cause of your problem. We may have to look at your database instead. Do you have any code that may be preventing the user from selecting a customer while not on a new record? However, as other have said, you might hold off on working with your forms until you get the table design done, in case it might mean redesigning the forms again based on the new/correct table structure.
 

PuzzledNH

Registered User.
Local time
Today, 04:50
Joined
Nov 4, 2019
Messages
36
Do you have any code that may be preventing the user from selecting a customer while not on a new record?

Not that I am aware of. But, I am not very familiar with visual Basic.
When I open the VB macro the only code I see is as follows:

Option Compare Database
Private Sub Product Line_AfterUpdate()
Me.[Product Line].DefaultValue = """" & Me.[Product Line].Value & """"
End Sub


I suspect this was from before I started to restructure the original table1.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:50
Joined
Oct 29, 2018
Messages
21,357
Not that I am aware of. But, I am not very familiar with visual Basic.
When I open the VB macro the only code I see is as follows:

Option Compare Database
Private Sub Product Line_AfterUpdate()
Me.[Product Line].DefaultValue = """" & Me.[Product Line].Value & """"
End Sub


I suspect this was from before I started to restructure the original table1.
And that looks like it's for the Product textbox or combobox and has nothing to do with the Customer combobox. If you can post a sample db, we can try to help you look for the cause of the problem.
 

PuzzledNH

Registered User.
Local time
Today, 04:50
Joined
Nov 4, 2019
Messages
36
So I deleted the form and recreated it. The combo box is working as expected now. I am not sure what the issue was. However, everything is good. Perhaps some erroneous artifact was causing the issue and was cleared when the form was deleted.

Thank you for the assistance. Issue is closed.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:50
Joined
Oct 29, 2018
Messages
21,357
So I deleted the form and recreated it. The combo box is working as expected now. I am not sure what the issue was. However, everything is good. Perhaps some erroneous artifact was causing the issue and was cleared when the form was deleted.

Thank you for the assistance. Issue is closed.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom