VBA code on click chckbox on current event

[saledo2000]

Hi everyone,
I have a checkbox on a form with record from REG table, which I use to copy current records from one table to a temporary table which changes every day. I have set checkbox on click event to execute a VBA code to copy these record with append query:

Private Sub FL_chkbox_Click()
Dim FL_chkbox As String

If Me!FL_chkbox = True Then
DoCmd.OpenQuery "Append RegToFL"


ElseIf Me!FL_chkbox = False Then
DoCmd.OpenQuery "FL_Delete"

End If
Exit_here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_here


End Sub

But I have subform SF_FM in continuous view with records from another table FM where I would like to copy current records from SF_FM which are related to the first table REG (one to many).
I have found some code to count current records on a subform but how to combine it with my IF statement. Code is:

Dim rs As DAO.Recordset
With Me.SF_FM.Form
DoCmd.SetWarnings False
Set rs = .RecordsetClone 'Get underlying subform records
rs.MoveFirst 'Start at first record
Do Until rs.EOF 'Until last record
.Bookmark = rs.Bookmark 'Navigate to current record
DoCmd.OpenQuery "Append FMToFL"
rs.MoveNext 'Next record
Loop
End With
DoCmd.SetWarnings True

Thank you.

 

[theDBguy]

Hi. I think it would be easier to follow what you want if you could post a demo version of your db.

 

[Deleted member 147267]

What's not (also) clear is when you want the 'found' code to run - checkbox = True or checkbox = False.
Please use code tags (# on forum menu bar) when posting code. Some of what you have can be eliminated but without knowing the foregoing, I hesitate to suggest any such changes.

 

[bastanu]

Why don't you use the same approach for the related records (create a new append query limited to the records related to the one in the main form)?

Cheers,
Vlad

 

[saledo2000]

Hi
I would like on click event to copy current records on a form to a temporary table. When i Clear checkbox to delete these current records from temp. table. attached is demo db.

Thanks.

 

[isladogs]

Excuse me for not being a mind reader but you have three forms (one with no records). None of the forms have a checkbox so I've no idea what to look at.

Also why do you want to keep making and deleting temp tables. Doing so will cause database bloat and eventually may lead to instability/corruption.
Can you explain the reason for doing this as there is probably a better way.

 

[saledo2000]

Hi
there is a checkbox on a REG form called FL in the upper right corner.
The reason is for reporting purposes, because It change every day and do not want to mess with the main table REG. I would like to have it that way because DB will not be big and I found that solution the best.
Thanks

 

[isladogs]

Ah I see. Actually in the top left corner hidden behind the navigation pane.

If you really need a temp table it would be better to just empty it each day using a delete query then repopulate it with an append query. There will still be bloat but it is less likely to cause instability

In fact now I look at your code, that is what you are doing!

Anyway you are getting an error as you aren't referring to the name of your checkbox. Try this

Private Sub Food_List_chkbox_Click()

If Me.Food_List_chkbox = True Then
DoCmd.OpenQuery "Append RegToFL"
Else
DoCmd.OpenQuery "FL_Delete"
End If

Exit_here:
    Exit Sub
    
Err_Handler:
    MsgBox Err.Description, vbExclamation, "Error"
    Resume Exit_here
End Sub

 

[saledo2000]

Thank you,
That code is working fine but I need to copy current records from continuous sub form. if I add another append query it copies only first record. I have added a code to count how many records is current on a sub form, but do not know how to combine it with my code for chckbox.

Thanks.

 

[isladogs]

As the form and subform are both empty on my version, I'm sorry but I can't advise on what you need to do.
Do you get more than one record in the subform with the master/child field settings you have?

 

[Deleted member 147267]

Thank you,
That code is working fine but I need to copy current records from continuous sub form. if I add another append query it copies only first record. I have added a code to count how many records is current on a sub form, but do not know how to combine it with my code for chckbox.
Thanks.

That suggests the query was not correct. If you think about it, if a query is the source for a subform and that form has many records, then a query that can select several records can be converted into one that appends (copies) or updates several records. It's likely the best approach but if you're stuck on using code, here's what I threw together in the meantime (note, not that it's better than Isladog's - just more along the lines of what you seem to be asking for). Untested:

Private Sub FL_chkbox_Click()
Dim db As DAO.Database

On Error GoTo errHandler
Set db = CurrentDb
If Me.FL_chkbox Then '= True shouldn't be required
  db.Execute "Append RegToFL", dbFailOnError
  Dim rs As DAO.Recordset
  With Me.SF_FM.Form
    Set rs = .RecordsetClone 'Get underlying subform records
    If Not (rs.EOF And rs.BOF) Then
      rs.MoveFirst 'Start at first record
      Do Until rs.EOF 'Until last record
        .Bookmark = rs.Bookmark 'Navigate to current record
        db.Execute "Append FMToFL", dbFailOnError
        rs.MoveNext 'Next record
      Loop
    End If
  End With
Else
  db.Execute "FL_Delete", dbFailOnError
End If

exitHere:
On Error Resume Next
Set db = Nothing
Set rs = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Error"
Resume exitHere

End Sub

I'm assuming the query you want to run in the loop knows where to get the values from in the recordset clone. Note: the checkbox As String seems wrong.

 

[saledo2000]

On a form I would have one record which is set in REG table. On a sub form will have sometimes 1 or up to 8 records. I need to copy main parent record from a form and related records on a sub form.
For the next record on a form will have another related records on a sub form etc.

Thanks