Toggle Box and Queries

[frankbutcher]

Hi.
I have never used a toggle button on a form before, and have searched the previous threads, with this one (http://www.access-programmers.co.uk/forums/showthread.php?t=113115&highlight=toggle+button) being the most relevant I think.

I need a toggle button, so that when it is pressed, it runs one query, and when depressed, it runs another. Basically, it is used as a add/Delete button sort of thing....but the add is an append query and the delete is a delete query.

I can't seem to change the code in the above sample, can anyone help?

Many Thanks.

Frank.

 

[TedMartin]

When the Toggle Buton is clicked it has either the Value 0 or -1. So, your code on the click event should be something like this:

Make its Record Source Toggle1 in your forms table; unless you want the Toggle to be generic across all records, in which case leave it blank.

If toggle1.value = 0 then
Docmd.openquery "Q-Append"
else
Docmd.openquery "Q-Delete"
end if


Try it as they may need to be the other way around.

You can also amend the Caption as well:

Private Sub Form_Current()

'Toggle1.SetFocus
If Toggle1.Value = 0 Then
Toggle1.Caption = "Add record"
Else
Toggle1.Caption = "Delete record"
End If

End Sub

Private Sub Toggle1_Click()

'Toggle1.SetFocus
If Toggle1.Value = 0 Then
Docmd.openquery "Q-Append"
Toggle1.Caption = "Add record"
Else
Docmd.openquery "Q-Delete"
Toggle1.Caption = "Delete record"
End If

End Sub

 

[frankbutcher]

Ted.
Thats great....and the captions was a bonus!!

Thankyou very much.

Frank.

 

[TedMartin]

When you run the queries - which incidentally are called Action queries, you may get a warning message. If you want to switch them off, then as the first line of the toggle click event add

DoCmd.SetWarnings False
and before the Exit Sub.
DoCmd.SetWarnings True

 

[frankbutcher]

Ted.
Sorry to be cheeky, but you give me your help on one more thing?
What is happening, is that when I quit out the form and go back in and click on the toggle button, it is asking me if I want to add the records, even if they have been added before. The toggle button sorts of resets itself.

What is the best way to overcome this?

I had a couple of ideas.......

There is an ID field between the 2 tables, and I have tried to add code in so when the toggle button is pressed, it will check to see if that ID is already in the appended table......

I also thought about having another query and text box. The query will check for the ID in the appended table, "Yes" will appear in the text box if it does, and no if it doesn't......once again, got sort of half way then got stuck!!!

Can you be of any help?

Many Thanks.

Frank.

 

[TedMartin]

I think I understand.

Sound like you need another field on your table that is called DataEdited". It is a simple Yes/No that when edited the value is changed to Yes, which is -1)

Add this field to your form, make it Locked but remaining ENABLED and when the Edit Button is pressed, the code is. If you ever need to change this value, you will have to go into the table.

DataEdited.SetFocus
DateEdited.Value = -1

Now all you need is another piece of code, probably on the On Current event that is:

if DateEdited.Value = -1 then
btnEdit.locked = True
else
btnEdit.locked = False
end if

Hope this answers it. As ever with Access, you need to get your head around all the logic options and trap the ones that you spot. Its called debugging or something like that!!