Edit/Update function data type mismatch

[gaker10]

I have an Edit function with conditions to control what gets edited and how:

Private Sub butEdit_Click()


'check for data existence
    If Not (Me.frmEntrySub.Form.Recordset.EOF And Me.frmEntrySub.Form.Recordset.BOF) Then
        'Load existing entry into form
        With Me.frmEntrySub
            Me.txtAssembly = !Assembly
            Me.txtComponent = !Component
            Me.txtDescription = !Description
            Me.numAssemblyQty = !AssemblyQty
            Me.txtUOM = !UOM
            Me.numQtyA = !QtyA
            Me.numQtyB = !QtyB
            Me.numQtyC = !QtyC
            Me.dbPriceA = !UnitPriceA
            Me.dbPriceB = !UnitPriceB
            Me.dbPriceC = !UnitPriceC
'store ID in tag in case of change
            Me.ID.Tag = !ID
'change Add button caption
            Me.butAdd.Caption = "Update"
'disable Edit button
            Me.butEdit.Enabled = False
        End With
    End If
    
End Sub

Private Sub butAdd_Click()
'Add entry to list


'Insert vs Update options


'If the part is not in the list
    If Me.ID.Tag & "" = "" Then


'add the part to the list
    
    'Add main part
    CurrentDb.Execute "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, UnitPriceA, UnitPriceB, UnitPriceC) " & _
    " VALUES ('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "','" & Me.numAssemblyQty & "','" & Me.txtUOM & "','" & Me.numQtyA & "','" & _
    Me.numQtyB & "','" & Me.numQtyC & "','" & Me.dbPriceA & "','" & Me.dbPriceB & "','" & Me.dbPriceC & "')"
    
    
    'If there is an AlternateA, Add AlternateA
    If Not (Me.txtAlternateA & "" = "") Then
    CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
    " VALUES ('" & Me.txtAlternateA & "','" & Me.txtAltADescription & " /ALT" & "','" & Me.txtAltAUOM & "')"
    End If
    
    'If there is an AlternateB, Add AlternateB
    If Not (Me.txtAlternateB & "" = "") Then
    CurrentDb.Execute "INSERT INTO EntryFormTable(Component, Description, UOM) " & _
    " VALUES ('" & Me.txtAlternateB & "','" & Me.txtAltBDescription & " /ALT" & "','" & Me.txtAltBUOM & "')"
    End If
    
'update an already existing part


   ElseIf InStr(Me.txtDescription, " /ALT") > 0 Then
   
        CurrentDb.Execute "UPDATE EntryFormTable " & _
        " SET Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        " WHERE ID='" & Me.frmEntrySub!ID & "'"
        
        Else
    
    CurrentDb.Execute "UPDATE EntryFormTable " & _
        " SET Assembly='" & Me.txtAssembly & "'" & _
        ", Component='" & Me.txtComponent & "'" & _
        ", Description='" & Me.txtDescription & "'" & _
        ", AssemblyQty='" & Me.numAssemblyQty & "'" & _
        ", UOM='" & Me.txtUOM & "'" & _
        ", QtyA='" & Me.numQtyA & "'" & _
        ", QtyB='" & Me.numQtyB & "'" & _
        ", QtyC='" & Me.numQtyC & "'" & _
        ", PriceA='" & Me.dbPriceA & "'" & _
        ", PriceB='" & Me.dbPriceB & "'" & _
        ", PriceC='" & Me.dbPriceC & "'" & _
        " WHERE ID='" & Me.frmEntrySub!ID & "'"
            
            End If
    
'Clear form after add/update
    butClear_Click


'Refresh form
    frmEntrySub.Form.Requery


End Sub

If an alternate is selected to be edited, it populates the form correctly, but when an update is made, I get a run time error '3464': Data type mismatch in criteria expression where the highlighted code is in red above in the Add function. Alternates only have 3 fields that matter, regular parts can be edited in all fields. When I try to update a part that is not an alternate, I also get run time error '3061': Too few parameters. Expected 3. (Code in blue). These records should be reading 11 parameters.

[ItsMe]

For the first problem

This
" WHERE ID='" & Me.frmEntrySub!ID & "'"

Should probably be
" WHERE ID=" & Me.frmEntrySub!ID


EDIT:
And now that I look at it again, you may need to reference the form object within the subform Container.
" WHERE ID=" & Me.frmEntrySub.Form.ID

Can't remember....

[John_G]

Hi -

First, replace With Me.frmEntrySub with With With Me.frmEntrySub.form

Now, in this bit:


CurrentDb.Execute "UPDATE EntryFormTable " & _
" SET Component='" & Me.txtComponent & "'" & _
", Description='" & Me.txtDescription & "'" & _
", UOM='" & Me.txtUOM & "'" & _
" WHERE ID='" & Me.frmEntrySub!ID & "'"

Is ID a character string or numeric? If it is numeric, take the quotes off it:

CurrentDb.Execute "UPDATE EntryFormTable " & _
" SET Component='" & Me.txtComponent & "'" & _
", Description='" & Me.txtDescription & "'" & _
", UOM='" & Me.txtUOM & "'" & _
" WHERE ID=" & Me.frmEntrySub!ID

that is probably the cause of the error.

For the error in the code in blue, that error usually indicates that you have misnamed a table or query field somewhere.
(PriceA, PriceB, PriceC should be UnitPriceA, UnitPriceB, UnitPriceC maybe?)



Another possible cause is that you have the values for ID and AssemblyQty in quotation marks - are they both really strings, or are they numeric?

HTH

John

[gaker10]

ID is numeric. Autonumber to be specific. AssemblyQty is also numeric. I keep my QtyA, B, and C as text because sometimes it will be blank, which Access does not like when it's set as numeric. You were right about the UnitPrice though.

Both of your suggestions fixed it Something weird happens when I edit now though. If I edit the description, it wraps <div> and </div> around my description. My descriptions are set to RichText format because at some point I'm going to have to automate the formatting of the concatenation of " /ALT" to bold face and a different color when I add a part and alternates.

[ItsMe]

What is the process of editing the description?

[gaker10]

The description is autofilled based on the part numbers in the designated fields (Component, AlternateA, AlternateB). AlternateA and AlternateB's description get " /ALT" appended to the end. It's also in the code in post #1 near the top of the code block.

When I move this finished table into Excel, the people who look up prices for these parts need it to be obvious which parts are alternates. The standard is that somewhere around the part (I chose the description for the freedom in text type) is " /ALT" and it is boldfaced and some obnoxious or highlighting color. They like a dark-ish red. I would like to automate this. I tried wrapping it in <b> <\b> but it just shows up in Excel as: "<b> Some description </b>", not "Some description". Also, I don't know how to wrap in a color format.

[ItsMe]

I know there is a way to insert objects into Excel, I just do not know how. Insertion some sort of Active x/OLE may be a solution.

I do not see a way to use HTML or Rich Text in a Cell via formatting. Perhaps the best solution is to store your data as text and use a custom function to add formatting when viewed within the DB. Additionally, when exporting, use automation via VBA to format the cells in your Excel Worksheet. Export plain text and then format the cells in the worksheet.

[gaker10]

I tried using a Find and Replace in the exported Excel, but it boldfaced the entire cell contents instead of just the " /ALT". Also tried SUBSTITUTE in excel but I couldn't figure out exactly how it worked. Plus it didn't do anything anyway.

[ItsMe]

I am not an expert with Excel so I am not sure what you are referring to. My suggestions have to do with using VBA to create an instance of Excel within your Access app to "Automate" Excel and manipulate properties of the worksheet. My preference is to store the most simple form/type of data in tables and then manipulate/format the data for display and or export purposes.

[gaker10]

I agree with your preference. The manipulation is giving me trouble though. I'm not familiar with the use of OLE or ActiveX.

If I could keep my code as it is (enter simple text) and automate the manipulation after the entry, that would be preferable. I don't see how objects would help in this though. Objects are loaded/utilized at the start, yes?

[ItsMe]

Have you ever copied something from a web page, like a table, and pasted it into Excel. Some objects that are not cell sometimes get copied over. This is something that may be an option but I would not know where to start.

The simplest, IMO, would be to import text to your spreadsheet and then format the appropriate cells. For this, you would need to do Automation. The Object you would create would be an instance of Excel, not an OLE object. It would better be defined as an object or even a class.

Post 9 and 10 have some example code to do this. You would need to add code to access the Format property of cells. The example I posted is not perfectly how I would do it. I refactored existing code from someone else.
https://www.accessforums.net/program...tml#post210391

I could not find any real good examples of formatting but this thread might point you in the correct direction. It has a formatting line in it.
https://www.accessforums.net/program...ess-40312.html

[gaker10]

I agree. I'll give your suggestions a try