Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14

    Unhappy Data Type Conversation Error when USING VBA

    Good day,

    I have the following code attached the the click event of a button.

    I am receiving a Data Type Conversion on this part of the code:

    !Quantity = "txt" & (a)



    Any help to solve this will be appreciate.

    Code:
    Private Sub Command17_Click()
    
    Dim db As Database
    Dim rs As Recordset
    Dim DocNr, TransType, Site, TransDate, HireStart, Comments As String
    'I am naming going to do the code in three parts for the three column in the form
    Dim a, b, c As Long
    'Column 1
    Dim cmb1, cmb2, cmb3, cmb4, cmb5, cmb6, cmb7, cmb8, cmb9, cmb10, cmb11 As String
    Dim txt1, txt2, txt3, txt4, txt5, txt6, txt7, txt8, txt9, txt10, txt11 As String
    
    
    Set db = CurrentDb()
    'this opens up the database the form is in. Leave as is.
    Set rs = db.OpenRecordset("tbl_transactionlist")
    'and this opens up the recordset you want to add the records to. It is the name of the table in "s
    DocNr = Nz(Me.txt_docnr, "")
    TransType = Nz(Me.cmb_transtype, "")
    Site = Nz(Me.cmb_site, "")
    TransDate = Nz(Me.txt_transdate, "")
    HireStart = Nz(Me.txt_hirestart, "")
    Comments = Nz(Me.txt_comments, "")
    
    
    
    
    'Main If to check if document information is there
    If DocNr = "" Then
    MsgBox ("You have not completed the Document Number. Please include this and retry.")
    Exit Sub
    Else
    End If
    If TransType = "" Then
    MsgBox ("You have not selected a transaction type. Please include this and retry.")
    Exit Sub
    Else
    End If
    If Site = "" Then
    MsgBox ("You have not chosen a site for the transaction. Please include this and retry.")
    Exit Sub
    Else
    End If
    If TransDate = "" Then
    MsgBox ("You have not selected a transaction date. Please include this and retry.")
    Exit Sub
    Else
    End If
    If HireStart = "" Then
    MsgBox ("You have not selected a hire start date. Please include this and retry.")
    Exit Sub
    Else
    End If
    
    
    
    
    'this is for the main document information
    With rs
    'This implies that there is rs before each . or !
        .AddNew
        !Doc_nr = DocNr
        !Trans_Type = TransType
        !Site = Site
        !Date_Of_Transaction = TransDate
        !Hire_Start_Date = HireStart
        !Comments = Comments
        .Update
        .Close
    End With
    
    
    'this will save the first column to the table
    Set rs = db.OpenRecordset("tbl_transactions")
    'and this opens up the recordset you want to add the records to. It is the name of the table in "s
    
    
    a = 1
    cmb1 = Nz(Me.Combo1, "")
    cmb2 = Nz(Me.Combo2, "")
    cmb3 = Nz(Me.Combo3, "")
    cmb4 = Nz(Me.Combo4, "")
    cmb5 = Nz(Me.Combo5, "")
    cmb6 = Nz(Me.Combo6, "")
    cmb7 = Nz(Me.Combo7, "")
    cmb8 = Nz(Me.Combo8, "")
    cmb9 = Nz(Me.Combo9, "")
    cmb10 = Nz(Me.Combo10, "")
    cmd11 = Nz(Me.Combo11, "")
    txt1 = Nz(Me.Text1, "")
    txt2 = Nz(Me.Text2, "")
    txt3 = Nz(Me.Text3, "")
    txt4 = Nz(Me.Text4, "")
    txt5 = Nz(Me.Text5, "")
    txt6 = Nz(Me.Text6, "")
    txt7 = Nz(Me.Text7, "")
    txt8 = Nz(Me.Text8, "")
    txt9 = Nz(Me.Text9, "")
    txt10 = Nz(Me.Text10, "")
    txt11 = Nz(Me.Text11, "")
    
    
    With rs
    
    
    For c = 1 To 11
    If "cmb" & (a) <> "" Then
    If "txt" & (a) <> "" Then
    .AddNew
        !Doc_nr = DocNr
        !Item_Code = "cmb" & (a)
        !Quantity = "txt" & (a)
        .Update
    Else
    End If
    Else
    End If
    a = a + 1
    Next c
        .Close
    End With
    
    
    
    
    MsgBox "This transaction has been saved.", vbOKOnly
    DoCmd.Close acForm, "frm_capture_new_transaction", acSaveNo
    
    
    Set db = Nothing
    Set rs = Nothing
    'These close the recordset
    End Sub

  2. #2
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Is quantity a txt value?


    Sent from my iPhone using Tapatalk

  3. #3
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14
    Hi Andy,

    Quantity in the table is a number. On the form i used a textbox to allow users to enter the number and save to the table using the SAVE Button.

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Can I suggest a line above the error line
    Debug.print isnull("txt" & (a))


    Sent from my iPhone using Tapatalk

  5. #5
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14
    Hi,

    I did that and it is just giving the exact same error?

  6. #6
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    In the immediate window what's it reporting


    Sent from my iPhone using Tapatalk

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try

    !Quantity = me("txt" & a)

    also suspect your itemcode is being filled with the same value 'cmb1' rather than the value in the cmb1 control

  8. #8
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14
    Hi Ajax,

    I tried that, and this is the error that I receive: Error 2465 - Microsoft Access can't find the field 'txt1' as referred to in your expression. I know that there is a txt1 textbox.

    You are also right. The Item_Code field in the table is being filled with cmb1.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps the textbox is not in the same form as the code?

  10. #10
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14
    I know it is.

    Can I upload the database and you have a look at it? Or is that not allowed on this website?

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Happy to look. Advanced button


    Sent from my iPhone using Tapatalk

  12. #12
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14
    Strange, it keeps saying failed to upload the file once it reached 100%.

    Here is a link to the file on Google Drive if that is okay:

    https://drive.google.com/file/d/0BwK...ew?usp=sharing

  13. #13
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Did you zip the file first?


    Sent from my iPhone using Tapatalk

  14. #14
    SybRiN is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2017
    Posts
    14
    Prota Services Material Capture Sheet.zip

    Okay Sorry I am noob at this. There is the attachment.

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I know that there is a txt1 textbox
    perhaps you need glasses - I can see a text1 textbox

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

Similar Threads

  1. Data type Error
    By Glenn_Suggs in forum Access
    Replies: 3
    Last Post: 01-25-2016, 11:30 AM
  2. data type mismatch error
    By accessmatt in forum Queries
    Replies: 10
    Last Post: 09-24-2014, 05:43 PM
  3. Data type mismatch error
    By nigelbloomy in forum Programming
    Replies: 1
    Last Post: 08-01-2012, 09:19 AM
  4. Replies: 1
    Last Post: 05-11-2012, 10:59 AM
  5. Combo Box FK Data Type Error
    By southcraven in forum Database Design
    Replies: 7
    Last Post: 02-02-2012, 09:42 PM

Tags for this Thread

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