Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-02-2019, 01:19 PM   #1
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Combo Box works for New Records but not for Record Updates

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.
Attached Images
File Type: jpg RelationshipCapture.JPG (46.4 KB, 13 views)

PuzzledNH is offline   Reply With Quote
Old 12-02-2019, 01:24 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,413
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Combo Box works for New Records but not for Record Updates

Hi. I think we'll need to see how you have set up the Combobox. For example, what Row Source are you using?
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 12-02-2019, 01:41 PM   #3
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,418
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Combo Box works for New Records but not for Record Updates

I made 2 points in your prior thread (https://www.access-programmers.co.uk...d.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.

plog is offline   Reply With Quote
Old 12-02-2019, 08:21 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,298
Thanks: 15
Thanked 1,597 Times in 1,517 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Combo Box works for New Records but not for Record Updates

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 12-03-2019, 09:56 AM   #5
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Combo Box works for New Records but not for Record Updates

Quote:
Originally Posted by plog View Post
I made 2 points in your prior thread (https://www.access-programmers.co.uk...d.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.


Quote:
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.

Quote:
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.


Quote:
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.



Quote:
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.


Quote:
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.
Attached Files
File Type: pdf 60CM Inspection Report.pdf (55.0 KB, 2 views)
PuzzledNH is offline   Reply With Quote
Old 12-03-2019, 10:03 AM   #6
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Combo Box works for New Records but not for Record Updates

Quote:
Originally Posted by Pat Hartman View Post
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.
Attached Images
File Type: jpg RelationshipCapture2.JPG (42.3 KB, 2 views)
PuzzledNH is offline   Reply With Quote
Old 12-03-2019, 10:13 AM   #7
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Combo Box works for New Records but not for Record Updates

Quote:
Originally Posted by theDBguy View Post
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.

PuzzledNH is offline   Reply With Quote
Old 12-03-2019, 10:22 AM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,413
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Combo Box works for New Records but not for Record Updates

Quote:
Originally Posted by PuzzledNH View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 12-03-2019, 01:46 PM   #9
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 31
Thanks: 4
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Combo Box works for New Records but not for Record Updates

Quote:
Originally Posted by theDBguy View Post
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.
PuzzledNH is offline   Reply With Quote
Old 12-03-2019, 01:59 PM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,413
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Combo Box works for New Records but not for Record Updates

Quote:
Originally Posted by PuzzledNH View Post
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.

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Log updates and new records, duplicate records how to select the most recent sk84gtspd Queries 2 08-06-2015 08:25 AM
Combo Box Updates aball65 Forms 3 01-18-2012 08:45 AM
Combo box search no longer works, and forms do not show records!!! pityocamptes Forms 11 01-21-2011 01:25 PM
[SOLVED] Combo Box updates stopka Forms 4 07-15-2005 02:46 PM
Combo Box -- Not In List Code Works but takes me to knew record LittleGlory Forms 7 07-24-2002 08:37 AM




All times are GMT -8. The time now is 11:33 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World