Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Insert Into Statement Troubles

    HI all,
    Having issues with an Insert Into Statement. Attached is db, opens to TransactionFrm....
    Trying to insert two records into table AccountLedgerTbl and getting errors.


    It appears the GUID number doesnt want to go into that table? If I take it out from the insert into then
    I get asked for parameters for "description" and "Method" fields?
    Could someone take a look at this for me please...

    When i hit submit button, it should put two records in the table AccountLedgerTbl and i cant figure out
    what i have done wrong?
    Thank
    Dave

    Code:
    Private Sub CmdSubmit_Click()    If IsNull(CboFromAccountID) Or IsNull(CboToAccountID) Then
                MsgBox "You're missing some data!"
                Exit Sub
        End If
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, GUID, Description, Method, Debit) " & _
            "VALUES (" & TxtTransID & ", " & CboFromAccountID & ", " & TxtTransDate & ", " & TxtGUID & ", " & TxtDescription & ", " & TxtMethod & " , " & TxtAmount & ")"
        DoCmd.RunSQL "INSERT INTO AccountLedgerTbl (TransID, AccountID, TransDate, GUID, Description, Method, Credit) " & _
            "VALUES (" & TxtTransID & ", " & CboToAccountID & ", " & TxtTransDate & ", " & TxtGUID & ", " & TxtDescription & ", " & TxtMethod & " , " & TxtAmount & ")"
        DoCmd.SetWarnings True
     
        DoCmd.Close
    End Sub
    Account.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    TransactionFrm opens automatically and is already in edit mode - why?

    Text fields require apostrophe delimiters for parameters. Date fields require # delimiter.

    "VALUES (" & TxtTransID & ", " & CboToAccountID & ", #" & TxtTransDate & "#, '" & TxtGUID & "', '" & TxtDescription & "', '" & TxtMethod & "' , " & TxtAmount & ")"

    Why are you duplicating transaction data into AccountLedgerTbl?

    Why save both MethodOpt and Method in TransactionTbl?
    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
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Off the top I'd say don't use GUID for an object name because it is a data type. Alternatively, enclose it in brackets in your references and see what happens.
    An explanation of what to do with your form would help.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    and is already in edit mode - why?
    because a bound field is edited by code on Current event.
    The 4 checkbox fields should be in a type table and the value of that record as one field in the transaction table.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, I understand the technicality of why the form is in Edit mode, guess I should have clarified question as: Why allow programmatic edit of existing record when form opens and place record in edit mode?
    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.

  6. #6
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi all,
    Thank you for the help! I was able to get that insert into working with the exception of the GUID. I may should have explained it is an auto number and i not sure how to put that into the table field?
    Maybe I need to rethink that and just make a unique number for the transaction....????

    On the check boxes, i am basically using them to control the buttons in option group to be visible or not....

    I still dont understand how i am programically opening this up to be in edit mode?

    Thanks

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Because of lines in Current event like Me.TxtMethod = "Cash" setting value of textbox which places record in edit mode.
    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.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You cannot update an autonumber field like that. Are you sure you need a replication id for that field? It's the largest size of numeric data field that you could possibly choose.

    EDIT - the field you are changing is bound. That means whatever value might be there is over-written if something made it some other value. I fail to see the need for an option group to show an option and a bound text field for the same thing. Maybe unbind the textbox or just make the textbox the option label and do away with the option label.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    An INSERT action can set value of autonumber field, at least a normal autonumber field, although there should be little reason for this. However, GUID field in AccountLedgerTbl is just a text field so apostrophe delimiter should work. Again, why duplicate this data in multiple tables?

    Step debug code. TxtGUID does not show a value. In spite of what is displayed on form, code is not picking it up.
    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
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can set every value or just the initial one? Methinks it is the latter.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thanks,
    I can get rid of that number and just make up something with a random number in the qry. I thought that was an issue!
    Thanks
    I played for two days trying to get the option label to show up and gave up on that. I just dont understand how to pass it on to a different form.

  12. #12
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi June7,
    I am going to rewrite and get rid of the GUID # and just create a transaction random number to make that field. With that, the double entry is only there for the debit one account, credit one account. Take money from this account and then add money to this account. The subform in the account details frm shows only the related transaction to that account, weather it be a debit or credit.
    Still a work in progress for sure.....
    Thanks so much

  13. #13
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi Micron,
    The reaason why i made that option with a hidden txtbox field to show the lable was i couldnt figure out how to just put the label in the table for this. I want to see if a transaction is Credit / Debit, Cash, and so on when i view the
    account detail form and i didnt want the sub form to show a 1, 2, 3, 4 or 5.... I want it to show Cash, Credit/Debit, Check, Deposit, or EFT in that subform and i was unable to figure out how to put lable into a different table.

    Thanks

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not really sure what that means. If you used the textbox and did away with the label, why would you need the label? Textbox can look like a label and double as a bound field as well. Or you could keep the label, hide the textbox. Either way, you'd just need a Select Case block to make the textbox = "whatever" and then save the form data.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, here's an example of what I'm talking about where the frame is bound to Method field:
    Code:
    Select Case Me.FraMethod
        Case 1
            Me.TxtMethod = "Cash"
            Me.TxtChkNumber.Visible = False
        Case 2
            Me.TxtMethod = "Credit/Debit"
            Me.TxtChkNumber.Visible = False
        Case 3
            Me.TxtMethod = "Check"
            Me.TxtChkNumber.Visible = True
        Case 4
            Me.TxtMethod = "Deposit"
            Me.TxtChkNumber.Visible = False
        Case 5
            Me.TxtMethod = "EFT"
            Me.TxtChkNumber.Visible = False
        Case Else
            MsgBox "error message here"
    End Select
    Me.Dirty = False
    You would disable the frame click code (I don't think it was ever the correct event for this), plus you wouldn't need all of these in the Current event if you just bind to the Method field:
    If Me.FraMethod = 1 Then
    Me.TxtMethod = "Cash"
    Me.TxtChkNumber.Visible = False
    End If

    and you can set the label caption to the field value or more simply, just show the textbox and substitute it for the label as I mentioned.

    Click image for larger version. 

Name:	Cash.jpg 
Views:	14 
Size:	5.7 KB 
ID:	43264 Click image for larger version. 

Name:	cash2.jpg 
Views:	14 
Size:	6.5 KB 
ID:	43265
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 7
    Last Post: 05-10-2019, 10:40 PM
  2. Insert INTO SQL Statement
    By tecknologikal in forum Programming
    Replies: 3
    Last Post: 02-12-2019, 09:31 PM
  3. INSERT INTO statement
    By berderder in forum Programming
    Replies: 4
    Last Post: 06-03-2016, 06:00 PM
  4. Insert Into statement
    By TimMoffy in forum Programming
    Replies: 7
    Last Post: 07-13-2012, 07:10 AM
  5. Insert statement
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 08-19-2011, 02:20 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