Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262

    Unbound text box data type and "Delete" function

    So I have a database (table) of components (part numbers), and associated descriptions and units of measure. I have another table that has an associated form, where the form directly inputs description and units of measure based on selected component, and pops into a subform, which I consider a preview of the table. Assembly (combination of numbers, letters, and symbols) does not always have to be entered. The text box for Assembly is unbound so that I can get my code to work (somewhat). However, I need the data type to be a string, which does not seem to be a standard option. When I type in something like 009-01 and add it to my table, it shows up as 8 because it truncates any preceding 0's, and interprets "-" as subtraction. I noticed that when I put double quotes around anything in my Assembly text box, it comes out the preferred way A.K.A "009-01" comes out to 009-01. But, I want to automate this insertion of double quotes for the people who will be putting this info in the form and don't understand Access very well, or forget to put in double quotes manually. When I put in anything except numbers (including blank space), I get an error "Run-time error '3134': Syntax error in INSERT INTO statement." The error is found in function at the red-highlighted text.

    Code:
    Private Sub butAdd_Click()
    
    
    'Insert vs Update options
    
    
        If Me.txtComponent.Tag & "" = "" Then
    
    
    'add, clear form, and refresh
        
        CurrentDb.Execute "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, PriceA, PriceB, PriceC) " & _
        " VALUES(" & Me.txtAssembly & ",'" & Me.txtComponent & "','" & Me.txtDescription & "','" & Me.numAssemblyQty & "','" & Me.txtUOM & "','" & Me.numQtyA & "','" & _
        Me.numQtyB & "','" & Me.numQtyC & "','" & Me.dbPriceA & "','" & Me.dbPriceB & "','" & Me.dbPriceC & "')"
        
        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 Component=" & Me.txtComponent.Tag
        End If
        
        butClear_Click
        
        frmEntrySub.Form.Requery
    
    
    End Sub
    My other issue is when I delete records from my subform. I highlight a record in my subform to be deleted and I get an error "Run-time error '3061': Too few parameters. Expected 1."

    Code:
    Private Sub butDelete_Click()
    
    'check for existing record
    'yes on confirm: delete and refresh
    
    
        If Not (Me.frmEntrySub.Form.Recordset.EOF And Me.frmEntrySub.Form.Recordset.BOF) Then
        
            If MsgBox("Are you sure you want to delete?", vbYesNo) = vbYes Then
                
                CurrentDb.Execute "DELETE FROM EntryFormTable " & _
                " WHERE Component=" & Me.frmEntrySub.Form.Recordset.Fields("Component")
                
                Me.frmEntrySub.Form.Requery
            End If
        End If
        
    End Sub
    The functions are linked to buttons. The point of this is to create a list of materials for an assembly. I'd like to make Component a primary key so that duplicates aren't entered. Component is the only field that will not be repeated in one specific list of materials. The table that is filled will eventually be exported to excel for calculation and printing purposes.

    So, how do I make an unbound text box accept strings without manually inserting "", and what's wrong with my Delete function?



    Thanks in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Text field type needs apostrophe delimiters for its value. Number types do not use delimiters. I presume the qty and price fields are number type so remove the apostrophes. Presume txtAssembly is a text field and need apostrophes:

    " VALUES('" & Me.txtAssembly & "',

    Date fields use # delimiter.

    Try:

    WHERE Component=" & Me.frmEntrySub!Component
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Thank you so much for the quick reply!

    Your suggestion for apostrophes solved my Assembly text problem!

    I tried your second suggestion though and got the same error with the same highlighted text, after I changed it of course.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Is Component a text field? Same issue with apostrophes.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Technically, I have two Component objects, where the text component box is filled in when a selection from the component combo box is made. But the one being cleared is a text box. Where would the apostrophes go in the statement?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    But you are referencing field, not controls. What do you mean by 'being cleared' - deleted?

    Around the parameter same as in the INSERT sql.

    WHERE Component='" & Me.frmEntrySub!Component & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Sorry, I meant deleted And your code suggestion fixed it! Thankk youuu!!!

  8. #8
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Ok now I have one more problem hehe

    I have an Edit command which is activated when I highlight a subform record and hit the button, then changes are made. While in edit mode, the add button caption changes to "Update", which is pressed when changes are finished being made, and the edit button is disabled during this time. The Edit button is re-enabled after record is updated.

    When I highlight a record and activate the Edit command, I get this error (another apostrophe issue? >.<):

    "Run-time error '-2147352567 (80020009)': The value you entered isn't valid for this field"

    Code:
    Private Sub butEdit_Click()
    
    
    'check for data existence, store component in tag in case of change
    'change Add button caption, disable Edit button
        
        If Not (Me.frmEntrySub.Form.Recordset.EOF And Me.frmEntrySub.Form.Recordset.BOF) Then
            With Me.frmEntrySub.Form.Recordset
                Me.txtAssembly = .Fields("Assembly")
                Me.txtComponent = .Fields("Component")
                Me.txtDescription = .Fields("Description")
                Me.numAssemblyQty = .Fields("AssemblyQty")
                Me.txtUOM = .Fields("UOM")
                Me.numQtyA = .Fields("QtyA")
                Me.numQtyB = .Fields("QtyB")
                Me.numQtyC = .Fields("QtyC")
                Me.dbPriceA = .Fields("PriceA")
                Me.dbPriceB = .Fields("PriceB")
                Me.dbPriceC = .Fields("PriceC")
                
                Me.txtComponent.Tag = .Fields("Component")
                
                Me.butAdd.Caption = "Update"
                
                Me.butEdit.Enabled = False
            End With
        End If
        
    End Sub

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    No, SQL statements are not involved.

    Try:

    With Me.frmEntrySub
    Me.textAssembly = !Assembly

    I don't understand what you want. Why are you populating main form controls with data from subform?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    It's the other way around Form entries populate the subform. The highlighting of the record in the subform is to indicate which record to edit by populating the record from the subform into the main form, then editing it from the main form, then replacing the subform record with the new edited main form data.

    From your suggestion I get a compile error: Method or data member not found.

    Code:
    Private Sub butEdit_Click()
    
    
    'check for data existence, store component in tag in case of change
    'change Add button caption, disable Edit button
        
        If Not (Me.frmEntrySub.Form.Recordset.EOF And Me.frmEntrySub.Form.Recordset.BOF) Then
            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 = !PriceA
                Me.dbPriceB = !PriceB
                Me.dbPriceC = !PriceC
                
                Me.txtComponent.Tag = .Fields("Component")
                
                Me.butAdd.Caption = "Update"
                
                Me.butEdit.Enabled = False
            End With
        End If
        
    End Sub

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You are populating unbound controls in main form for using to create a new record in subform with the INSERT action?

    Why are you setting Tag property with Component value? Use the same syntax for referencing the field as in the preceding lines.
    Last edited by June7; 06-13-2014 at 10:39 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Sorry I forgot to mention this: the actual update of data occurs in my add function.

    Code:
    Private Sub butEdit_Click()
    
    
    'check for data existence, store component in tag in case of change
    'change Add button caption, disable Edit button
        
        If Not (Me.frmEntrySub.Form.Recordset.EOF And Me.frmEntrySub.Form.Recordset.BOF) Then
            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 = !PriceA
                Me.dbPriceB = !PriceB
                Me.dbPriceC = !PriceC
                
                Me.txtComponent.Tag = .Fields("Component")
                
                Me.butAdd.Caption = "Update"
                
                Me.butEdit.Enabled = False
            End With
        End If
        
    End Sub
    THEN:

    Code:
    Private Sub butAdd_Click()
    
    
    'Insert vs Update options
    
    
        If Me.txtComponent.Tag & "" = "" Then
    
    
    'add, clear form, and refresh
        
        CurrentDb.Execute "INSERT INTO EntryFormTable(Assembly, Component, Description, AssemblyQty, UOM, QtyA, QtyB, QtyC, PriceA, PriceB, PriceC) " & _
        " VALUES('" & Me.txtAssembly & "','" & Me.txtComponent & "','" & Me.txtDescription & "'," & Me.numAssemblyQty & ",'" & Me.txtUOM & "'," & Me.numQtyA & "," & _
        Me.numQtyB & "," & Me.numQtyC & "," & Me.dbPriceA & "," & Me.dbPriceB & "," & Me.dbPriceC & ")"
        
        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 Component=" & Me.txtComponent.Tag
        End If
        
        butClear_Click
        
        frmEntrySub.Form.Requery
    
    
    End Sub

    I don't know vba syntax too well :\ I've coded in other languages but not this one.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Same issue with apostrophe delimiters in the UPDATE sql.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    Figures And where do those go?

  15. #15
    gaker10 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    262
    I think I fixed it with:

    Code:
    Private Sub butEdit_Click()
    
    
    'check for data existence, store component in tag in case of change
    'change Add button caption, disable Edit button
        
        If Not (Me.frmEntrySub.Form.Recordset.EOF And Me.frmEntrySub.Form.Recordset.BOF) Then
            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 = !PriceA
                Me.dbPriceB = !PriceB
                Me.dbPriceC = !PriceC
                
                Me.txtComponent.Tag = !Component
                
                Me.butAdd.Caption = "Update"
                
                Me.butEdit.Enabled = False
            End With
        End If
        
    End Sub

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-11-2014, 11:57 PM
  2. Replies: 3
    Last Post: 05-31-2013, 04:32 PM
  3. How to format a "Number" Data type
    By undee69 in forum Access
    Replies: 4
    Last Post: 12-16-2012, 10:20 PM
  4. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM
  5. Replies: 2
    Last Post: 05-17-2011, 02:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums