Updated data type from Integer to Long Integer, rebuilt relationships, still getting "value not valid" (1 Viewer)

breezett93

New member
Local time
Today, 06:23
Joined
Jun 7, 2021
Messages
20
I recently had to convert a field in all of my tables because we hit the maximum limit. I rebuilt the relationships exactly to how they were before the change.
So, the user clicks a button that starts the code below. Somewhere, Access is remembering what the old data type was and not allowing the new values to be used. This is the first case that is happening in VBA. For all previous cases, I would just go into Record Source in design view, copy the query text, delete it, and paste it. Then the value not valid errors went away.

Error is triggered on the bolded line below, which makes sense because that is the field that has been updated.


Code:
DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE DISTINCTROW IvcDetTbl INNER JOIN OrdDetTbl ON (IvcDetTbl.OrdItm = OrdDetTbl.OrdItm) AND (IvcDetTbl.OrdId = OrdDetTbl.OrdId) SET OrdDetTbl.Cmplt = Yes WHERE ((IvcDetTbl.IvcId=[Forms]![IvcFrm001]![IvcId]) AND (IvcDetTbl.Cmplt=Yes))"
        DoCmd.SetWarnings True
   
    'Update Charges
       
        [B][OrdId] = [OrderID][/B]
        [MiscChg] = [MiscCharge]
        [CrtDelChg] = [CrtCharge]
        [MiscChgDesc] = [MiscChargeDesc]
        [MiscChgCode] = [MiscChangeCode]


I just don't know the process to fix this as I have verified that OrdId in OrdDetTbl is the same data type as OrderID in IvcDetTbl and the relationship between them has been restored.[/code]
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:23
Joined
Oct 29, 2018
Messages
21,358
Have you tried refreshing the linked tables? Just a thought...
 

moke123

AWF VIP
Local time
Today, 07:23
Joined
Jan 11, 2013
Messages
3,852
Error is triggered on the bolded line below, which makes sense because that is the field that has been updated.
The only field being updated is - SET OrdDetTbl.Cmplt = Yes (if that query works)

What is this part supposed to be doing and where are the values coming from?
Code:
    'Update Charges
     
        [B][OrdId] = [OrderID][/B]
        [MiscChg] = [MiscCharge]
        [CrtDelChg] = [CrtCharge]
        [MiscChgDesc] = [MiscChargeDesc]
        [MiscChgCode] = [MiscChangeCode]
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:23
Joined
Feb 28, 2001
Messages
27,001
You didn't mention the time frame of these changes and you didn't mention editing the code, only doing things with datatypes. If that view is correct then your code may remember the old settings because code that has been compiled and not changed will not recompile automatically.

Have you tried to decompile and then recompile the code?

 

breezett93

New member
Local time
Today, 06:23
Joined
Jun 7, 2021
Messages
20
The only field being updated is - SET OrdDetTbl.Cmplt = Yes (if that query works)

What is this part supposed to be doing and where are the values coming from?
Code:
    'Update Charges
    
        [B][OrdId] = [OrderID][/B]
        [MiscChg] = [MiscCharge]
        [CrtDelChg] = [CrtCharge]
        [MiscChgDesc] = [MiscChargeDesc]
        [MiscChgCode] = [MiscChangeCode]
So the purpose of this button is to apply values/charges from OrdDetTbl to IvcDetTbl. So, all the fields on the left side except for OrdId would be null, and the fields on the right side have the values/charges that will be applied to IvcDetTbl.
 

moke123

AWF VIP
Local time
Today, 07:23
Joined
Jan 11, 2013
Messages
3,852
It would probably help to see the whole procedure.
 

breezett93

New member
Local time
Today, 06:23
Joined
Jun 7, 2021
Messages
20
It would probably help to see the whole procedure.
For the button being pressed?

Code:
Private Sub AddOnCharges_Click()
    
    DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE DISTINCTROW IvcDetTbl INNER JOIN OrdDetTbl ON (IvcDetTbl.OrdItm = OrdDetTbl.OrdItm) AND (IvcDetTbl.OrdId = OrdDetTbl.OrdId) SET OrdDetTbl.Cmplt = Yes WHERE ((IvcDetTbl.IvcId=[Forms]![IvcFrm001]![IvcId]) AND (IvcDetTbl.Cmplt=Yes))"
        DoCmd.SetWarnings True
    
    'Update Charges
        
        [OrdId] = [OrderID]
        [MiscChg] = [MiscCharge]
        [CrtDelChg] = [CrtCharge]
        [MiscChgDesc] = [MiscChargeDesc]
        [MiscChgCode] = [MiscChangeCode]
    
    Me!CustCombo.SetFocus
    
        DoCmd.RunCommand acCmdSaveRecord
        SetInvoiceForm

End Sub
 

breezett93

New member
Local time
Today, 06:23
Joined
Jun 7, 2021
Messages
20
You didn't mention the time frame of these changes and you didn't mention editing the code, only doing things with datatypes. If that view is correct then your code may remember the old settings because code that has been compiled and not changed will not recompile automatically.

Have you tried to decompile and then recompile the code?

The change to the data type was made on Monday. Prior to that, this button has always worked and never had a problem. That's why I'm confident it's related to the data type change. This code has been untouched.

Is it possible to just decompile and recompile the one form?
 

Users who are viewing this thread

Top Bottom