UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Combo Box From Lookup Table, Updating Look Up Value Text In Lu Table To Lu Id, Access 2016    
 
   
jcm420a
post Sep 13 2019, 09:14 AM
Post#1



Posts: 2
Joined: 12-September 19



Greetings, long time viewer, first time poster.

Scenario is this: (Windows 10, Access 2016)

I have form with a text field - Event Name (Pulled from existing value in Events table) and 3 combo boxes (created from Wizard, select key field and lookup value text, hide key field, save value for later use) on a form that each pull values from a respective lookup table

Lookup table structure:

tblCategories: CategoryID (PK, Integer) | CategoryName (Text 255)
tblDirectorate: DirectorateID (PK, Integer) | DirectorateName (Text 255)
tblLocation: LocationID (PK, Integer) | LocationName (Text 255)

*What should happen:

1. User completes the form, selects values from each of the combo boxes (Category, Directorate, Location) clicks Save Event button. (Tested - Values passed to SQL statement correspond to values from combo box)
2. Update SQL Statement runs, updates Events table and sets values in table to values on form (Update SQL Syntax correct, I did a debug.print to immediate window, copy and paste to QBE, run SQL, Update runs correctly)

What does happen:

1. User completes the form, selects values from each of the combo boxes (Category, Directorate, Location) clicks Save Event button. (Tested - Values passed to SQL statement correspond to values from combo box)
2. Update SQL Statement runs, updates Events table and sets values in table to values on form (Update SQL Syntax correct, I did a debug.print to immediate window, copy and paste to QBE, run SQL, Update runs correctly)
3. (Here's the part that I can't explain) The value of CategoryName in tblCategories updates to the value of CategoryID
3a. I do not have cascade updates on any of the tables
3b. Nowhere in ANY of the code in the entire DB is there an SQL statement to update tblCategories
3c. Nowhere in any part of the DB is there a command to update the value of CategoryName in tblCategories
3d. Neither the values of [tblDirectorate].[DirectorateName] nor [tblLocations].[LocationName] update to their corresponding ID field.


I'm completely befuddled by this. I've deleted / recreated the combo box for the Category lookup, verified that the actions / properties for all 3 lookups are the same.

Has anyone previously experiences this, or can throw out some additional troubleshooting ideas?

Thanks!
Jordan

Go to the top of the page
 
GroverParkGeorge
post Sep 13 2019, 09:39 AM
Post#2


UA Admin
Posts: 35,881
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

First, I'm not sure why you need update queries. One of the great strengths of Access is the ability to bind forms directly to a table (or to queries based on a table). So, this form should be bound to the Event table. There should be Foreign Key fields in that Event table for CategoryID, DirectorateID and LocationID. Each of those fields should be bound to the appropriate Field, e.g. the combo box for CategoryID should be bound to the CategoryID field in the event table, and so on. No reason to run an update query to handle any of that. Just go with Access strong point.

Second, it sounds like the combo box for Category is actually bound to the CategoryName column, not to the CategoryID column. That would account for that problem.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
jcm420a
post Sep 13 2019, 10:33 AM
Post#3



Posts: 2
Joined: 12-September 19



GroverParkGeorge, thank you for the reply and guidance, and I'm sure by the time I figure this out, a considerable amount of education.

I can't post actual code, due to the DB residing on a non-public system.

Your reply resulted in me needing to explain further.

The form isn't bound to a table, it uses a joined query (non-updatable recordset) to pull current event values. Query joins Event Table (FKs/PKs) to LU tables to get the text from the LU table to display as a static current values, with an Unbound Combo box with the Bound column as the ID field, showing the Text value associated with the ID field, which is something I've done more times than I can count.

I read all of the form values into variables, then run the update SQL, which runs fine.

I did some further debugging/testing, and put and END directly after I set the Variables equal to the form value - e.g. Category = cboCategory.value, and for some reason, it updated tblCategories.CategoryName to tblCategories.CategoryID


-----And, just now, I, for the heck of it, changed the variable named Category to CategoryValue, and for some odd reason the problem stopped.
-Is Category a reserved word I'm unaware of?
Go to the top of the page
 
theDBguy
post Sep 13 2019, 10:45 AM
Post#4


Access Wiki and Forums Moderator
Posts: 76,387
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UA! welcome2UA.gif

I don't recall "category" being a reserved word. I doubt that it is, but I could be wrong.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Sep 13 2019, 10:52 AM
Post#5


UA Admin
Posts: 35,881
Joined: 20-June 02
From: Newcastle, WA


Thanks for the background.

As a general rule, I think Access works best when its native strengths (e.g. bound forms and bound controls) are exploited.

I don't see any advantage to joining the lookup tables to the main event table for the form's recordsource. All you really need are the combo boxes on the form. They are bound to the relevant foreign key fields from those lookup tables (i.e. cboCategoryID is bound to field CategoryID in the event table). Because the row source for combo boxes allows you to display whatever additional field(s) you need from the lookup table, there's no need to make that second field, "CategoryName", part of the form's recordsource. It's in the combo box's row source for display purposes only in the interface anyway. It shows up in Column(1) of the combo box, and that's all you need, is it not? Keeping in mind, by the way, that columns in list and combo boxes are indexed starting with 0, that means the 1st column on the left is Column(0) and the second column from the left is Column(1). Typically, we bind the Combo box to Column(0), and that's the one which has the Foreign Key (e.g. CategoryID). Because the only reason to include the value field (i.e. "CategoryName") is to provide a visual clue to the user, it really only needs to be available to see in the visible column of the combo box.

All of that said, if your particular design requires this unbound approach, it can be implemented and the required code written to do the extra work once you have worked out the bugs, which it sounds like you've accomplished.

I don't know that "Category" is a reserved word per se, but it was possibly part of the problem.

Congratulations on solving the problem.

Continued success with your project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
missinglinq
post Sep 13 2019, 12:35 PM
Post#6



Posts: 4,637
Joined: 11-November 02



I agree with George, as I usually do! Using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t! You don't need Unbound Forms to do

  1. Data Validation
  2. Formatting Data before it's Saved
  3. Deciding whether or not to actually Save a New or Edited Record


which are the most common reasons given. Nor are they needed for another dozen reasons I've seen people give!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything...even the most mundane tasks!

Bottom line is…with Bound Forms you end up writing code for a few specialized situations, such as #1-#3, as listed above…and with Unbound Forms you have to write code for virtually everything that needs to be done!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

  • You can create an EXE file which gives total protection to your code/design
  • You can distribute the db to PCs without a copy of Access being on board
  • Your data security is far, far better than anything you can do in Access


Don't misunderstand me...there are a few, specialized situations, where an Unbound Form is preferable...but anyone who routinely uses them for everything has simply made a bad choice in deciding to work in Access.

And no...'category' isn't on any list of Reserved Words I have in a short & dirty db I made for checking words.

Linq ;0)>

--------------------
Hope this helps!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 06:51 PM