Multi-table query not updateable

mjdemaris

Working on it...
Local time
Today, 01:36
Joined
Jul 9, 2015
Messages
426
Hello all!
I would like to create a query from several tables, display the records on a datasheet, and be able to change values, as one would in an Excel spreadsheet.

This code only uses three table, I changed the JOINs so that I could see all the records:

Code:
SELECT [T-Parts].MasterNum, [T-Parts].Item, [T-Parts].CategoryID_FK, [T-PartLocations].SummerLevel, [T-PartLocations].WinterLevel, [T-PartLocations].PartLocID, [T-PartLocations].BinID_FK, [T-Parts].Discontinued
FROM ([T-Categories] RIGHT JOIN [T-Parts] ON [T-Categories].CategoryID = [T-Parts].CategoryID_FK) LEFT JOIN [T-PartLocations] ON [T-Parts].MasterNum = [T-PartLocations].MasterID_FK;

This code (enter another table) is NOT updateable:

Code:
SELECT [T-Parts].MasterNum, [T-Parts].Item, [T-Parts].CategoryID_FK, [T-PartLocations].SummerLevel, [T-PartLocations].WinterLevel, [T-PartLocations].PartLocID, [T-PartLocations].BinID_FK, [T-Parts].Discontinued, [T-SupplierPartNums].PartNumber
FROM ([T-Categories] RIGHT JOIN ([T-Parts] LEFT JOIN [T-SupplierPartNums] ON [T-Parts].MasterNum = [T-SupplierPartNums].MasterID_FK) ON [T-Categories].CategoryID = [T-Parts].CategoryID_FK) LEFT JOIN [T-PartLocations] ON [T-Parts].MasterNum = [T-PartLocations].MasterID_FK;

What I don't understand is why can't I update the info in one or two tables, when the fourth table is not seemingly affected. Me thinks this has something to do with how a recordset is created/designed, but is there a solution?

Thanks in advance.
 
Well, sneuberg, I discovered that, yes, I have at least one condition mentioned by Mr. Browne that prevents the recordset from being updateable. So, I am attempting to create a read-only datasheet that the user will have to click on a field, which will open a popup form, to edit the data.

However, I have trouble getting all the info to display that I need. Perhaps you could help. I could send a zipped file of the db...
 
Sure upload it now, but I'm tied up right now. I'll look at it tomorrow.
 
No problem. I'll be here a few more hours, then back on Monday.

Nice tag line!
 

Attachments

Well, sneuberg, I discovered that, yes, I have at least one condition mentioned by Mr. Browne that prevents the recordset from being updateable. So, I am attempting to create a read-only datasheet that the user will have to click on a field, which will open a popup form, to edit the data.

However, I have trouble getting all the info to display that I need. Perhaps you could help. I could send a zipped file of the db...

I can't seem to locate the form in which you are trying this. Which one is it?
 
The form is question is F-GeneralSearchDS - it is the datasheet. The form F-GeneralSearch houses the DS, and will either filter or replace the record source depending on what the user wants to view/do.

Mike
 
I'm not sure if it helps much, but I got the Part Number, Bar Code and Company to display in the form by adding the appropriate tables to the record source query. The modified database is attached,

I suppose you could use the on click events of the textboxes to do what you want. You would have to add any primary keys to the forms record source that you would need. But in the click events you obviously know which field and table you are considering and you could get the primary key and data values from the textboxes. Then put all of that in Tempvars add open a popup form. You would have to do that for every textbox. Seems like a lot of work. Maybe there's someway of generalizing the code you will need. I suggest starting a new thread on this topic.
 

Attachments

hey sneuberg, appreciate the help.
I got frustrated enough with trying to do something that is most likely not going to work the way the user wants it to, so he is going to have to click on a field in the datasheet to open a form and change the values!
Thanks!

I'll take a look at it this week.
 
Well, I'm back with the same problem. The user is adamant that he be able to view the data in a spreadsheet fashion and edit it then and there!
So, I have been able to use a query to create a temporary table and allow him to edit that data, but then I have the problem of taking the edits and updating the real data, especially the calculated field!
The calculated field is the quantity left of an item, based on previous transactions. If he changes the calculated value (albeit in the temporary table), how am I going to get that data into the field that really matters?
In creating this temporary table, I take PKs from a couple of tables as fields, so it does not have a PK itself, nor is it related to any others, as this would most likely cause all sorts of confusion.
 
How about describing the issue independent of Access, spreadsheet etc---just simple English? And a sample of what a typical record would look like and what the user wants to do to that record--show the after adjustment by user view.

Need to see the problem and WHAT is being requested. Not HOW the user would design it.
 
Well jdraw,
I'll see what I can do...
Imagine, if you will, three rooms filled with parts. Most of the parts are only kept in one room or the other, however there are a few parts in more than one room. (The reason being is that they are about 200 meters apart.)

One guy manages 90% of the parts (room 3 is used and managed by one person only.) And 90% of these parts are open to company employees. All they need to do is walk in, find what they need, and sign out each part in the current Excel program and provide a count of what's left (or estimation).

Unfortunately, not everyone signs every part out, so what we end up with is a discrepancy in what is really on the shelf and what the inventory program says we have. So the manager has to review each sign out where the discrepancy is sizable, visually check the item on the shelf, and change the quantity in the program to reflect the actual on shelf.

Now, he does this updating by using a spreadsheet in Excel, so he can change a value and run on down the list.

I'll grab some pics for sampling...
 
Here is a sample from the current system.

The number that the manager adjusts (on a regular basis, as he has authority to change other info) is the On Hand column. This section only shows data for one room, so imagine this data duplicated for three rooms.

So, the database I have set up so far uses a location reference (PartLocID) to track transactions and calculate quantities. (On Hand)

As you can see, we can see who last signed out a part and when and when the last inventory (stock take) was done.

Hope this helps paint the picture!
 

Attachments

  • ExcelMPD01.PNG
    ExcelMPD01.PNG
    20.3 KB · Views: 180
I don't know if you have seen this from Allen Browne, but the concepts and approach are often referenced with any Inventory/stock control application.
 
Yes, which is what I have set up, but I'm trying to give the user some of the similar functionality. He is almost 73, stubborn, overworked, and has done it like this for 7 years!
 
Wow, so it does exist! I'll see what comes of today...

Thanks!
 

Users who are viewing this thread

Back
Top Bottom