Modify a Button in a Form so it Toggles a Table Value

[Jimbo54]

I am working on a database with a table recording aircraft details ("Aircraft"). Amongst other things, this table contains an identifying field (An alphanumeric string unique to the aircraft) for "Airframe", and another (toggleable yes/no) field "Complete?" showing whether the record for the particular airframe is complete. I need to create a simple form where users can select any aircraft in the table by airframe number and then click on a button to toggle the "Complete?" status in the "Aircraft" table.

I copied another, existing form, that allowed the user to select the airframe number from a pull-down, then display all the information related to the airframe in a new form by clicking on a button embedded within the form.



The new, copied form has a field that allows me to select each aircraft individually (no change from the original), but now I want to change the function of the button so it toggles the "yes/no" status of a field in the "Aircraft" table

The existing code "behind" the button looks like this:

Private Sub Select_Click()
On Error GoTo Err_Select_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Aircraft"
    
    stLinkCriteria = "[Airframe Number]=" & "'" & Me![Airframe Number] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Select_Click:
    Exit Sub

Err_Select_Click:
    MsgBox Err.Description
    Resume Exit_Select_Click
    
End Sub

Can anyone help me by suggesting what I need to do to the button code?

Apologies if this is a really stupid question, I haven't touched Access in years and when I originally wrote this database, it was much simpler. Its been modded by others and I'm a bit (a lot?) out of my depth.

Thanks

Jim

[June7]

You show code to open form filtered to record. Is that working? You want to add code that will automatically change Complete? field to opposite of what it already is?

If you don't want to open form and instead just change value in table, run an UPDATE action SQL.

CurrentDb.Execute "UPDATE Aircraft SET [Complete?]= Not [Complete?] WHERE [Airframe Number] = '" & Me.[Airframe Number] & "'"

Advise not to use spaces nor punctuation/special characters in naming convention. Better would be AirframeNum and IsComplete.

[Jimbo54]

Hi June7,

You said

You show code to open form filtered to record. Is that working? You want to add code that will automatically change Complete? field to opposite of what it already is?

If you don't want to open form and instead just change value in table, run an UPDATE action SQL.

The code to open form filtered to record is working fine...I wrote that about 20 years ago when I built the DB, and when I had a much better idea of how Access works

Recently, I copied a form that allowed airframe selection and then caused the selected airframe details to display by button click, intending to use the existing airframe selection mechanism, then having selected the right 'frame, modify the button function so that it changed the state of the "Completed?" field...so Yes, you are right, I want to replace working code behind the button with new code that will automatically change the "Completed?" field to opposite of what it already is when the button is clicked

I placed the following code behind the button as you suggested:

Private Sub Select_Click()
On Error GoTo Err_Select_Click


    
CurrentDb.Execute "UPDATE Aircraft SET [Completed?]= Not [Completed?] WHERE [Airframe Number] = '" & Me.[Airframe Number] & "'"
    

Exit_Select_Click:
    Exit Sub

Err_Select_Click:
    MsgBox Err.Description
    Resume Exit_Select_Click
    
End Sub

...but it responds with "Too few Parameters. Expected 1".


I understand that this error happens when the field name(s) in the command do not match the table field name(s), i.e. a field name in the command is wrong or perhaps the table is missing the field altogether.

The table "Aircraft" definately has the columns "Completed?" and "Airframe Number" and its also definately called "Aircraft", so I'm at a bit of a loss as to whats going on (I checked the spelling).

Sorry to ask for help, I know I should be doing this myself, RTFM-ing and visiting Dr Google, but I've been doing that and all thats happened is that I've been spinning my wheels for days.

Thanks

Jim

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.