Update Table through Inner join query? 1

[Snappy2873]

Hello everyone,
First of all, i have a form with it's data source being the result set of the following query.

SELECT Silo_Clientelle.*, TANDEM_DLT.SER_NUM, TANDEM_DLT.PT_NUMBR, TANDEM_DLT.STATUS
FROM Silo_Clientelle INNER JOIN TANDEM_DLT ON (Silo_Clientelle.TSIOrderNumber = TANDEM_DLT.TDM_TSI_IN) AND (Silo_Clientelle.TandemOrderNumber = TANDEM_DLT.TDM_ORD_NO)
WHERE ((TANDEM_DLT.STATUS) = "P");

The processing of this form consists of entering shipping information and then clicking a button to execute the "Shipment complete" macro which will then finalize and populate relevant data to the corresponding tables. The reasoning for this query was to limit the shipment personel from shipping a record unless it had a "P" (pending) status. The query pulls in records with only this status for validation purposes. The original query was as follows:

SELECT * FROM Silo_Clientelle

This result set gave free reign to the records in the database and would allow the shipment personel to ship a record before it became "P" status, hence the reasoning for the query at the top of the page. Now the first query limits the results and choices of the "find record" but creates an error when trying to "update" through a query. The second query "original", allows for the update in the record and another problem in that it "overrides" the status setting. You might ask "why dont you add a parameter to the original??", because I had to join the other table to it for the correct result set. Should I throw an "update" statement in the new query? This might sound like a mess but any reccomedations or ideas would br greatly appreciated.

Thanks,
snappy

 

[Tranman]

Snappy,
My guess is that you are not including the primary key (maybe it does not exist), of the TANDEM_DLT table, in your query.

Without having the primary key in the query, and thus in the underlying RecordsetClone object, the system does not know which row to update when you key new information on the form and attempt to update. The solution to this is to include the primary key field(s) in the query, whether or not you display it(them) on the form.

Select table1.*, table2.* from table1 inner join table2 on fieldA = fieldA1 and fieldB = fieldB1 where...

At least that is how I solved what seemed to be a similar problem a couple of months ago. The lack of a primary key is one of the main reasons people get forms/recordsets that are not updateable.

Feel free to let me know if you continue to have problems and I will give you a hand.

Good Luck,
Paul

 

[cmmrfrds]

Are you using Access 2000?
Is the database Access or SQL Server or other?
Is this an Access Project (adp) or mdb file?

Yes, you should have a primary key to update the record. It is not always necessary depending on the back end database and the method/product used to do the update. If the back end database is linked tables through ODBC, then the primary key is required.

 

[Snappy2873]

Hello Tranman/cmmrfrds,
In regards to Tranman: The Serial number is the primary key in the TANDEM_DLT table. Like you said, i would not need to visually display the serial number (SELECT TANDEM_DLT.SER_NUM etc...) but, somehow include it in the join statement so the recordset will know the record in question to update. Right? Do I need to define a recordsetclone object to fix this issue? I'll do some testing and respond with the results. Thanks for your help Tranman, you put me eons ahead!!!

In regards to cmmrfrds: I'm using Access 97' on a Win2k Advanced Server OS. I have a Win2k/XP dual boot situation. My Win2k is primarily used to host my SQL Server 7.0 SB but has the old office on to maintain and update all of the company Access Inventory DB's that are linked together. The XP OS has all the new utilities for XP and is primarily used as a "ghost drive". All of the Access DB's are .mdb files and are a front/backend combo. Built in VB components allow for easy VBA programming and efficient functionality. I'm currently replicating all company Access DB's in SQL and will move to a pure VB front end as well as SQL backend in the upcoming months. I will try the Primary key issue, do some testing, and reply with my results. Thanks for your input cmmrfrds!!!

 

[Tranman]

Snappy,
The recordsetclone object already exists. It contains the underlying data that is the immediate source of the information that is displayed on your form.

Like this: the form opens and runs the query that is the data source of the form. The results of the query are placed in a recordset object. The recordset is copied into another recordset object, which is the recordsetclone. The form itself expresses the data from the recordset. The recordset is not available for programmatic updating, but the recordsetclone is. However, the recordsetclone is not inherently synchronized to the form, and if you update it, you must first synchronize the two (recordsetclone.bookmark = form.bookmark).

When you run your shipment complete macro, what kind of stuff is in there? Do you run a module that does a SQL update to the table(s)? Trying to form a mental picture.

Paul

 

[Snappy2873]

Hello Tranman,
These are the three queries that are called when the "Shipment Complete" macro is fired. They are called in this order:

PendingToShipQuery: Syntax

UPDATE Inventory_Transaction INNER JOIN TANDEM_DLT ON Inventory_Transaction.SerialNumber = TANDEM_DLT.SER_NUM SET TANDEM_DLT.STATUS = "C", TANDEM_DLT.SHELF = Null, TANDEM_DLT.TDMSHPDATE = Date(), TANDEM_DLT.TDM_ORD_NO = [forms]![silo_clientelle].[tandemordernumber], TANDEM_DLT.TDM_CUST = [forms]![silo_clientelle].[shipto], TANDEM_DLT.TDM_TSI_IN = [forms]![silo_clientelle].[tsiordernumber]
WHERE (((TANDEM_DLT.STATUS)="P&quot AND ((TANDEM_DLT.SHELF) Is Not Null) AND ((TANDEM_DLT.TDMSHPDATE) Is Null) AND ((Inventory_Transaction.SystemNumber)=[forms]![silo_clientelle]![systemnumber]));

qryUpdateTandemShipInfo: Syntax

UPDATE TANDEM_DLT INNER JOIN (SHIPPING_INFO INNER JOIN Inventory_Transaction ON SHIPPING_INFO.TSINUMBER = Inventory_Transaction.TsiOrderNumber) ON TANDEM_DLT.SER_NUM = Inventory_Transaction.SerialNumber SET TANDEM_DLT.TDMSHPDATE = Date(), TANDEM_DLT.TDM_ORD_NO = [forms]![silo_clientelle]![tandemordernumber], TANDEM_DLT.TDM_CUST = [forms]![silo_clientelle]![shipto], TANDEM_DLT.TDM_TSI_IN = [forms]![silo_clientelle]![tsiordernumber], TANDEM_DLT.CITY = [forms]![silo_clientelle]![city], TANDEM_DLT.STATE = [forms]![silo_clientelle]![stateprovince], TANDEM_DLT.COUNTRY = [forms]![silo_clientelle]![country], TANDEM_DLT.CUSTOMER = "TANDEM"
WHERE (((Inventory_Transaction.TsiOrderNumber)=[forms]![silo_clientelle]![tsiordernumber]));

qryUpdateTransactionDate: Syntax

UPDATE Inventory_Transaction SET Inventory_Transaction.TransactionDate = Date()
WHERE (((Inventory_Transaction.SystemNumber)=[forms]![silo_clientelle].[systemnumber]) AND ((Inventory_Transaction.TsiOrderNumber)=[forms]![silo_clientelle].[tsiordernumber]));


The problem lies in the PendingToShipQuery because it bombs when there is not a "P" in the status. If there is no "P" the Shipment complete process is not completed. So you see the value of validation in this situation. Operations has to finish their processing before the shipment complete macro is run. Unfortunately, the standards for shipping and recieving have been poor, currently it has been my job to validate situations in order to keep integrity amongst the records, hence the modifications. When I, "synchronize the two (recordsetclone.bookmark = form.bookmark)", is the syntax defined in the form load event?
Thanks,
snappy

 

[Snappy2873]

Hello again Tranman,
After testing and redesigning my query, I cant join the primary in the JOin statement the only like fields are the order number and the shipping number. I can select it and display it all day just no direct operability between the primary Key (Ser_Num) in the TANDEM_DLT and the fields in the Silo_Clientelle table. Any Recommedations?
Thanks,
Chris AKA snappy

 

[Tranman]

Chris,
When I start getting beaten up by a problem like this, I usually try to break it down into simple pieces, and then try to figure out each piece.

Your PendingToShip update query is fairly complicated, but you should be able to replicate it in the graphical query design tool. Just substitute fields like [Enter Sys Nbr] for the [forms]![silo_clientelle]![systemnumber] in the criteria row; etc. Then when you run the query it will prompt you for a value for [Enter Sys NBr] and you can key it in.

Once you get it working right, you can copy and paste the SQL into your macro, then substitute the form field names for the phony ones (like Enter Sys Nbr) that you put in to start with.

As to why you can't join the serial number in the TANDEM_DLT to the serial number in the other table, it must be because there is no Inventory Transaction with the same number. Could one be text and the other a number?

If I'm understanding your problem right, (it is getting clearer all the time), it looks like what you really need to do is to code a function that verifies that there is a Pending transaction to process and get out of Dodge if there isn't one. So what you would do is code a function like the following and call it with a runcode command in your macro. If they tried to update a transaction that was not status "P" they would get an error message, and if there was a "P" transaction present, the function would go ahead and run the three update queries. I have not used the DAO much lately, but this will be pretty close to what you need to do to accomplish a "prequery" to find out if you have a Pending row for updating:

Public Function xxx()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("Select * from Inv_trans inner join .... where status = 'P'.....&quot
Select Case rs.EOF
Case False 'Not EOF--P record is present
'Ok to process update
'Put first Update query here
'Put second Update query here
'Put third Update query here
'Update queries have the form:
'CurrentDb.Execute "Update tablename inner join.......
Case True 'P record not found
MsgBox "Rec status not P. Cant upd.", vbOKOnly, "Nope"
End Select
End Function

I was previously under the impression that you were going to update the tables from fields on the form, and that is why I was talking about the recordsetclone. If you are going to update with a SQL statement, that is best done with the Execute method of the Application object (the way it is illustrated above).

Let me know if I'm on the right track.

Paul

 

[Snappy2873]

Hello Paul,
Here are some tests and the results that followed:
First of all, the result set of my data source is the root of the problem. I can only join TANDEM_DLT and Silo_Clientelle by the order number and the shipping number which creates a problem in the recordset when trying to update. These fields are not the primary key which is probably why the "recordset is not updatible". The status field in TANDEM_DLT is the parameter that is key to the completion of the whole shipping process, but you see, the status field has to be joined in to make the validation. if I didnt have to use a join, I would have no problem updating a single table.

Secondly, I tried pulling a function in that was embedded in a module, to no avail. Will still try this method again.

Thirdly, I tried hard coding the function within the form, in the onclick of the command to run the "shipment complete" macro. It said it was updating, but no rows were affected.

And lastly, I'm working on the runcode option within the "shipment complete" macro. Where do I actually put the function to call it in (is there a global asa in Access?)? I tried to build it in the expression builder but had some errors as well. I'm moving to a solution that would be "client" side validation, meaning once the user has looked up a record, the information is filled in and they click the "shipment complete" button, the function fires and checks the record on hand for validation (this means i would have to instigate my join at this point to have the status field from the other table... ). Once the record is checked for status (true or false), commence process or redirect focus back to finding another record that will process under these parameters. This solution could be hard coded or pulled in from the function location.

What is your opinion of this situation?

Anyway, thanks for your help Paul..
Chris