Results 1 to 15 of 15
  1. #1
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    If checkbox is checked allow query to run

    I have a continuous form that has a checkbox on it so that whatever records you have checked off, they will be entered into the table. checkbox is named misselect User can check off which ones they want and then the query would only append those selected records. Here is my code to run query. Pretty sure I have this wrong. I also need to be able to do a select all for a batch mode.


    If Me.misselect = True Then 'Purpose: Append Missed Events To tblEvent.
    End If
    Else


    If Me.misselect = False Then
    Exit Sub
    End If

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Qappendtotblevent"

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I don't think a continuous form is how you want to approach this. If you use a continuous form you'll basically have to run through your entire dataset to see if the flag is set, then append the record to your secondary table then clear the flag again. A multi select list box is easier to handle in terms of selected and unselected items.

    if you go the way you're going (I do not know your structure) your code would be incorrect though.

    Code:
    If Me.misselect = True Then                        'Purpose: Append Missed Events To tblEvent.
         'This section runs your append record if misselect = true
         DoCmd.SetWarnings False                 
         DoCmd.OpenQuery "Qappendtotblevent"
         DoCmd.SetWarnings True
    Else
         'This section runs if if misselect = false
         Exit Sub
    End If
    
    End Sub

  3. #3
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Okay, I think you are correct, a listbox is better. I created one and made it multilist. So now, how do I code it so that I can run my code after making my selection(s)?
    Here is the Code List for the Listbox which is List0 I will be checking off misselect for my selection(s), it is a Yes/No Data Type set to True/False format.

    SELECT [tblmissedtransactions].[EventID], [tblmissedtransactions].[bank], [tblmissedtransactions].[Payee], [tblmissedtransactions].[TransDate], [tblmissedtransactions].[ChkNo], [tblmissedtransactions].[enter], [tblmissedtransactions].[MyDte], [tblmissedtransactions].[misselect] FROM tblmissedtransactions;


    Here is my new code :
    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim ctl As Control
    Dim varItem As Variant

    On Error GoTo ErrorHandler

    Set db = CurrentDb()
    Set rs = db.OpenRecordset("tblmissedtransactions", dbOpenDynaset, dbAppendOnly)

    'make sure a selection has been made
    If Me.List0.ItemsSelected.Count = 0 Then
    MsgBox "Must select at least 1 Item"
    Exit Sub
    End If


    'add selected value(s) to table
    Set ctl = Me.List0ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

    ErrorHandler:
    Select Case Err
    Case Else
    MsgBox Err.Description
    DoCmd.Hourglass False
    Resume ExitHandler
    End Select
    For Each varItem In ctl.ItemsSelected
    rs.AddNew
    rs![ChkNo] = ctl.ItemData(varItem)
    rs![dteentered] = ctl.ItemData(varItem)
    rs![MyDte] = ctl.ItemData(varItem)
    rs![enter] = ctl.ItemData(varItem)
    rs![misselect] = ctl.ItemData(varItem)
    'rs!OtherValue = Me.txtOtherValue
    rs.Update
    Next varItem
    DoCmd.Beep

    DoCmd.SetWarnings False 'Purpose: Append Missed Events To tblEvent.
    DoCmd.OpenQuery "Qappendtotblevent" 'Update Query.
    If Me.Dirty Then Me.Dirty = False
    DoCmd.SetWarnings True
    Last edited by burrina; 01-29-2013 at 10:06 AM. Reason: New Code

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    here's code for cycling through selected item in a mult-select list box (this assumes the primary key for the list box is the bound value of the list box):

    Code:
    Dim VarItem as variant
    Dim PK as long (or string if the primary key of your list box is a string)
    
    For each VarItem in List0.ItemsSelected
             PK = List0.ItemData(VarItem)
    
        'From here use the PK of the listbox to build your append query rather than having a static variable
       
    next VarItem

  5. #5
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    Update Query Not Working

    I am not quite sure how to do that? I am having trouble making my Update query work though. Here are the screenshots and also the sql for the query. Nothing happens when I run it, no data is changed.

    UPDATE tblmissedtransactions SET tblmissedtransactions.enter = True, tblmissedtransactions.misselect = False, tblmissedtransactions.MYDte = Date(), tblmissedtransactions.dteentered = Date(), tblmissedtransactions.dtemod = Date(), tblmissedtransactions.tmemod = Time(), tblmissedtransactions.lastmodified = Now(), tblmissedtransactions.ChkNo = [ChkNo]="Next_Custom_Counter", tblmissedtransactions.missev = False, tblmissedtransactions.missdte = Null
    WHERE (((tblmissedtransactions.EventID)=[Forms]![frmmissedeventsListBox]![EventID]));
    Attached Thumbnails Attached Thumbnails UpdateQuery.jpg   ListBox.jpg  

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    tblmissedtransactions.ChkNo = [ChkNo]="Next_Custom_Counter"

    WHERE (((tblmissedtransactions.EventID)=[Forms]![frmmissedeventsListBox]![EventID]))

    these are two of your problems

    In the first you are setting ChkNo to be a string that is '[ChkNo]="Next_Custom_Counter"' which I don't think is what you want

    Secondly, this what I said before, you can't run your update from a static query if you use a list box you have to cycle through the values in the list box that are selected, create your SQL statement based on the value of the bound column of the list box then execute the SQL statement. So if you use the itemselected set of code I gave you your SQL statement WHERE clause would be something like:

    Code:
    dim sSQL as string
    dim db as database
    
    set db = currentdb 
    
    sSQL = "UPDATE tblmissedtransactions SET "
    ssql = ssql & "tblmissedtransactions.enter = True,  "
    ssql = ssql & "tblmissedtransactions.misselect = False, " 
    ssql = ssql & "tblmissedtransactions.MYDte =  #" & Date() & "#, "
    ssql = ssql & "tblmissedtransactions.dteentered = #" & Date() & "#,  "
    ssql = ssql & "tblmissedtransactions.dtemod = #" & Date() & "#, "
    ssql = ssql & "tblmissedtransactions.tmemod =  #" & Time() & "#, "
    ssql = ssql & "tblmissedtransactions.lastmodified = #" & Now() & "#,  "
    ssql = ssql & "tblmissedtransactions.ChkNo = #FIX THIS PART OF YOUR STATEMENT
    ssql = ssql & "tblmissedtransactions.missev = False, "
    ssql = ssql & "tblmissedtransactions.missdte =  Null "
    "WHERE (((tblmissedtransactions.EventID)= " & PK & "))"
    
    db.execute sSQL
    
    db.close
    you would put the sql statement string building inside the loop of items selected and run the thing whenever you wanted to perform an update, for instance in the ON CLICK event or ON DOUBLE CLICK event of the list box or have a separate button if you wanted to do your multi select first

  7. #7
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    Sql code error

    Okay, ChkNo i.e. Check Number is a field in the table CounterTable with the field being named NextAvailableCounter. It has a Module that it is called from. It is also a field in my table tblEvent and looks up the next check number. How do I code this to advance to the next check number form your code?

    Function Next_Custom_Counter()

    On Error GoTo Next_Custom_Counter_Err

    Dim rs As ADODB.Recordset
    Dim NextCounter As Long

    Set rs = New ADODB.Recordset

    rs.Open "CounterTable", CurrentProject.Connection, adOpenKeyset, adLockOptimistic 'Open the ADO recordset.

    NextCounter = rs!NextAvailableCounter 'Get the next counter.

    rs!NextAvailableCounter = NextCounter + 1
    NextCounter = rs!NextAvailableCounter
    rs.Update

    'MsgBox "Next available counter value is " & Str(NextCounter)
    rs.Close

    Set rs = Nothing

    Next_Custom_Counter = NextCounter

    Exit Function

    _________________________________________________
    Here is amended Code:
    Dim sSQL As String
    Dim db As Database

    Set db = CurrentDb

    sSQL = "UPDATE tblmissedtransactions SET "
    sSQL = sSQL & "tblmissedtransactions.enter = True, "
    sSQL = sSQL & "tblmissedtransactions.misselect = False, "
    sSQL = sSQL & "tblmissedtransactions.MYDte = #" & Date & "#, "
    sSQL = sSQL & "tblmissedtransactions.dteentered = #" & Date & "#, "
    sSQL = sSQL & "tblmissedtransactions.dtemod = #" & Date & "#, "
    sSQL = sSQL & "tblmissedtransactions.tmemod = #" & Time() & "#, "
    sSQL = sSQL & "tblmissedtransactions.lastmodified = #" & Now() & "#, "
    sSQL = sSQL & "tblmissedtransactions.ChkNo = # Next_Custom_Counter"
    sSQL = sSQL & "tblmissedtransactions.missev = False, "
    sSQL = sSQL & "tblmissedtransactions.missdte = Null "
    "WHERE (((tblmissedtransactions.EventID)= " & EventID & "))" ''' THIS IS WHERE THE ERROR IS"""

    db.Execute sSQL

    db.Close
    Attached Thumbnails Attached Thumbnails SQLCodeError.jpg  
    Last edited by burrina; 01-29-2013 at 01:32 PM. Reason: New Code:

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    NextCounter = rs!NextAvailableCounter 'Get the next counter.

    Assuming this part gives you the next item in an autonumber indexed table what are you trying to do with this?:

    rs!NextAvailableCounter = NextCounter + 1
    NextCounter = rs!NextAvailableCounter
    rs.Update

    If you have an autonumber field you can't alter it, that's the whole point of having an autonumber field as a primary key.
    Using the code you had in post #3 if your table tblmissedtransactions has an autonumber primary key you do not need to figure it, it will generate that number itself.

  9. #9
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Only part of code that errors out is this: Counter code shows no errors. What Goes where the PK is ? I tried EventID still did not work.
    "WHERE (((tblmissedtransactions.EventID)= " & PK & "))"

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    oi vey

    ssql = ssql & "WHERE (((tblmissedtransactions.EventID)= " & PK & "))"

  11. #11
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Ok, NO errors. I tried the running it but nothing changed! It is an Update query, should it be an append query instead? Only things that need to change are checkboxes and dates, and of course a new check number. Any ideas ?

    Thanks for your help, it is very much appreciated.

  12. #12
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    MY Apologies, used wrong table, forgot about that in my haste, sorry! All is well.

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    How can you possibly have no errors, you still haven't fixed the chkno part of your sql statement.

    use debug.print ssql after it is built
    comment out the db.execute statement until you know your SQL statement is correct

    When the code runs you will see your SQL statement in the immediate window, cut and paste that into a SQL (query) statement and try to run it and see what happens, I'll bet everything I own that it fails.

    I still don't know why or what you are trying to do with Next_Custom_Counter, any ID field that is an autonumber will increment on it's own without the need for you to define it.

  14. #14
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    There is only one field in the table CounterTable and it is a PK and is named NextAvailableCounter. It also has a module that advances that number by one automatically without you having to do anything to it. This is being used for a check number counter set to NO duplicates and so it advances to the next check number. Yep, the code wont run in a query! It shows no erros in the immediate window if I am doing it right. I ran it against the table and nothing happened.

  15. #15
    burrina's Avatar
    burrina is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    Unhappy Sql code error

    Nevermind.
    Last edited by burrina; 01-29-2013 at 09:23 PM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-09-2012, 10:07 AM
  2. VBA for testing if checkbox is checked
    By rhewitt in forum Programming
    Replies: 0
    Last Post: 09-26-2012, 07:11 AM
  3. Display Text Only if Checkbox is Checked
    By Rick5150 in forum Forms
    Replies: 2
    Last Post: 09-16-2012, 09:19 AM
  4. If checkbox is checked, add X to a current value
    By INeedAboutTreeFiddy in forum Programming
    Replies: 4
    Last Post: 05-30-2012, 08:17 AM
  5. Replies: 22
    Last Post: 11-14-2011, 10:29 AM

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