Update record base on combo box choice and related tables

[Design by Sue]

In my database I have a table that keeps track of a package of items. The package is assigned a package type (counter display, end cap, half pallet, full pallet for example). The record of the association of the package and it's type is held in the main table.

Each of these package types is either a case or a pallet (counter display and end cap are cases and half and full pallets are pallets) This relationship is kept in another table (we will call it description table).

Now, based on the type of package and therefore it being a case or pallet a UPC and a GTIN number are assigned. The GTIN number is different if it is a case or is a pallet. I have a table that stores all of the UPC and GTIN numbers available in 3 columns, one for UPC, one for GTIN Case and one for GTIN Pallet (the UPC is a standard 12 digit and the GTINs are 14 digit -with the first 2 different to designate pallet or case. and all are based on the check digit formula necessary)

The user assigns the UPC and correct GTIN number by clicking a button which applies the next available UPC code to the package and determines if the description of the type (case or pallet) and inserts correct GTIN number into that field. I actually have all of this functioning correctly.

Now the problem. If a user changes the package type, and therefore changes the description, I need to add code to the update event of the combo box that gives the choices for package type that does the following:

Check to see if the original package type was a case or pallet (it's description) and if by changing the package type it is now changed to the other, update the record in the main table to the correct GTIN number based on the existing UPC Code.

OR as I write this, maybe the code could simply update the main table with the correct GTIN code based on the new description and the existing UPC code. This was I would not need to check for a change just do the update every time.

Any thoughts on this? What would be the best way to accomplish this? I am thinking an update statement but would appreciate any input on the entire concept and what would be the most sensible way to accomplish this.

Sure hope I explained this so you can understand what I am trying to do.

Sue

 

[burrina]

Can you not do this using the OnChangeEvent so that it Reflects the new values?
Are you running any kind of update or append query right now with the combo?

 

[Design by Sue]

That is the direction I am going in now (after update) But I am having a problem with the coding of doing the update. I am trying use some of the queries I did for adding the correct UPC/GTIN when they are adding a new set of numbers.

I can create a query to display the UPC and both GTIN numbers for the UPC code currently assigned to the record that is opened on the form. But when I try to create an update query that will take the information from that query and overwrite the existing records, the view of my update query is showing the actually GTIN that is assigned to the record. (i.e. If the existing record is for a case, and I enter the pallet GTIN as the update - the view of the query displays the case GTIN not the pallet GTIN)

I know this is confusing but maybe someone not so close to this will understand and point me in a correct direction.

Thanks for trying!

 

[burrina]

If the query you say is correct, then save it as a new query and then make it a update query, add your required criteria, such as Forms!cbosomething.Column(somecolumn).Value under whatever field needs to be updated.
I am a very visual person, co can't really picture this in my mind.

HTH

 

[Design by Sue]

Here is the update query that is not working correctly. What it is supposed to do is update the PP Table by overwriting the existing GTIN to the CASE GTIN as displayed in the UPC Query. The UPC Query does return the correct values, that are the UPC that is the same as in the edit form, the CASE GTIN and the PALLET GTIN for that UPC number. But when this runs, rather than displaying the CASE GTIN it displays the PALLET GTIN which is the one currently in the PP table for the PP ID on the form.


UPDATE [PP TBL] SET [PP TBL].[GTIN] = [UPC QRY]![CASE GTIN]
WHERE ((([PP TBL].[PP ID]) Like [Forms]![PP Edit FRM]![ID]));

 

[Design by Sue]

Burrina - the first query is working correctly it is the update query that is messing up and I guess I need to walk away because I can't see what is wrong at all - see my previous post for hopefully something more understandable.

 

[burrina]

Here is my approach to doing what you are trying. Maybe you adapt this to your needs.

'Insert into tblReportLog Form Values.

Dim strSQL As String

strSQL = "INSERT INTO tblReportLog (lrptdte,lsentto,lsubject,lmessage,lbcc, lrptname,loutputtype ) "
strSQL = strSQL & "VALUES (#" & Me.rdtesent & "#,'" & Me.EmailTo & "','" & Me.EmailSubject & "','" & Me.EmailMessage & "','" & Me.cbobcc & "','" & Me.txtrptname & Me.txtextension & "','""')"


CurrentDb.Execute strSQL, dbFailOnError

HTH - If not, maybe someone else can assist.

 

[Design by Sue]

Another weirdness I just found is that the queries will not run unless they are opened. I get an error asking for the parameters for each query as they are run. I just don't get this. These are similar queries that I use to assign the new UPC Codes and GTIN codes and they work just fine.

 

[Design by Sue]

Am I using the incorrect query - I thought an update was the correct one - I read that insert was just to add records? As you can tell I have to walk away and look at this in the morning and pray the fog clears!

Thanks for your response, I do appreciate your taking the time, but that looks very confusing to me.

 

[burrina]

Oops, Sorry! I was distracted. Was having a Forest Gump moment. This should be more suited to your needs.

DoCmd.Hourglass True
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblUserSecurity_Sec SET tblUserSecurity_Sec.pw = '" & Me.txtCnfPW.Value & "' " & _
"WHERE (((tblUserSecurity_Sec.userID)='" & Me.txtUserID.Value & "'));"
DoCmd.SetWarnings True
DoCmd.Hourglass False

Again, HTH ,,, Later!

 

[Design by Sue]

So Update is correct I gather. Again I do thank you but what you posted confuses me so my guess is:


DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [PP TBL] SET [PP TBL].[GTIN] = '" & [UPC QRY]![PALLET GTIN] & "' " & _
"WHERE ((([PP TBL].[PP ID])='" & [Forms]![PP Edit FRM]![ID] & "'));"
DoCmd.SetWarnings True

That gives a runtime error of 2465 "can't find field'|1' referred to in your expression"

 

[burrina]

Check your names and concatenation for errors.

 

[Design by Sue]

I copied and pasted the names directly from the code of the query SQL that was not working for me so I wouldn't make a typing error. Could it be because it is based on the (working) query results? (not sure how to add that select statement to the code.

Sue

 

[burrina]

I suggest you start by debugging this step by step. Using a backup copy of course. Start with just one criteria at a time and see what happens.Problems are a matter of elimination.

 

[Design by Sue]

I have gone over this so many times and can't find any problem in the code - though it seems to be in the first line. So I am wondering if using a query as part of the set statement - that is the only thing I can think of. Please if anyone else has looked at this post an can help I would really appreciate it.


DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [PP TBL] SET [PP TBL].[GTIN] = '" & [UPC QRY]![PALLET GTIN] & "' " & _
"WHERE ((([PP TBL].[PP ID])='" & [Forms]![PP Edit FRM]![ID] & "'));"
DoCmd.SetWarnings True

 

[Design by Sue]

Realize that this is now a query question so I will post this question in that forum to see if I can get an answer

Sue

 

[burrina]

Sue, I can't really wrap my head around this without a demo db so here is an example for you.

I hope this helps!