Programming a button on a Form

[Jim54]

I have an Access 2013 database. It has several tables, one of which is called "Aircraft". In that table, there are several fields, two of which are "Airframe Number" (unique identifier) and "Complete?" (yes/no checkbox). I know that there should not be spaces or punctuation used in field names but the reason for that is historical and not something that can be easily changed right now without a major rewrite.

I want to make a form that will allow me to select a specific airframe number and toggle the yes/no condition. I copied a form that worked, that allowed airframe selection and then caused the selected airframe details to display as a result of a button click. I intended to use the existing airframe selection mechanism (which works fine), then having selected the right 'frame, modify the (exiting) button function so that it changed the state of the "Completed?" field, so in essence, I all I needed to do is replace working code behind the button with new code that will change the "Completed?" field to opposite of what it already is, when the button is clicked

I placed the following code behind the button:

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 it's also definately called "Aircraft", so I'm at a bit of a loss as to whats going on (I checked the spelling). I have tried a number of different " and ' combinations, in case its the space or punctuation in the field names that is the problem, to no avail. Any help would be much appreciated.

Cheers

Jim

 

[theDBguy]

Hi Jim. Welcome to AWF!

The easiest way to troubleshoot this is assign your SQL statement into a String variable and then print its content into the Immediate Window. Then, copy and paste it into the query designer (in SQL view) and run it. Access should then give you a hint on where the problem lies.

 

[Ranman256]

dont use code to run sql, Use a query.
the query wont ask for the param if youre reading it off a form.
code will.

use: docmd.openquery "quMyQry"

 

[Jim54]

Guys, I realise this is where you are completely justified in telling me to F.O.A.D., but I'm trying to pick up Access after not having touched it in over 10 years, and never really being too clever with code...can you explain what you mean in words of one syllable or less, so that my addled old brain can grasp your meaning....

Thanks, and Sorry

Jim

 

[Gasman]

Instead of this

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

you do this

Dim strSQL as String
strSQL = "UPDATE Aircraft SET [Completed?]= Not [Completed?] WHERE [Airframe Number] = '" & Me.[Airframe Number] & "'"
Debug.Print strSQL
CurrentDb.Execute strSQL

Then when all is working, comment out/delete the Debug.Print line
The Debug.Print will show in the Immediate Window.

Also set a Breakpoint at a suitable line and then walk through the code line by line with F8, helps a lot I have found.?

HTH[/code]

 

[Jim54]

Hi Gasman,
Thanks for the quick response. I did as you suggested, but I still just got the "Too few Parameters. Expected 1"

I'll try a few more things, but any idea why this might be happening? The table ("Aircraft)"that the button is trying to act on has the following fields:

Thanks

Jim

 

[Gasman]

Please post the result of the Debug.Print using Copy and Paste,

 

[Gasman]

I can confirm your logic works at least.?

Sub testUpdate()
Dim strSQL As String
strSQL = "update table1 set fieldchk = not fieldchk where id =12"
CurrentDb.Execute strSQL
End Sub

That worked, so what is the value of Aircraft Number?
Try a hard coded value as well?

 

[Jim54]

I chaged the code to what you suggested, but the output on clicking the button was not changed, and there was no additional output.

This is the form window before clicking the button:

This is the full code behind the "Change Status" button:

and this is what appears after clicking the button:

There was nothing else...or am I looking in the wrong place?

 

[Gasman]

Yes. Issue a Ctrl +G keystroke and you should see the Immediate window and the output of the debug.print.
For now you could comment out the Execute line as that is always going to happen until we get to the bottom of this issue.

 

[Jim54]

I tried the Cntl G as suggested an all I got was an alarm chime. So I then removed the execute line so the button code looked like this:

Private Sub Select_Click()
On Error GoTo Err_Select_Click




Dim strSQL As String
strSQL = "UPDATE Aircraft SET [Completed?]= Not [Completed?] WHERE [Airframe Number] = '" & Me.[Airframe Number] & "'"
Debug.Print strSQL




Err_Select_Click:
    MsgBox Err.Description
    Resume Exit_Select_Click
    
Exit_Select_Click:
    Exit Sub

End Sub

Having done that, when I clicked the button, I got this:

The Microsoft Access window could not be resized, and I could not see anything in it, but on clicking "OK", I got this:

which in a perverse sort of way is right, because the code *should* work properly, I can't see syntax errors (or perhaps I'm not looking in the right place)

 

[Gasman]

If you have the code window open, and the press Ctrl + G you should get the immediate window like below

You can even issue it from the form, just not when code is running I believe?

You also need an Exit Sub before Err_Select_Click: as you are just falling into the err code, hence the displays

 

[theDBguy]

I tried the Cntl G as suggested an all I got was an alarm chime. So I then removed the execute line so the button code looked like this:

Private Sub Select_Click()
On Error GoTo Err_Select_Click




Dim strSQL As String
strSQL = "UPDATE Aircraft SET [Completed?]= Not [Completed?] WHERE [Airframe Number] = '" & Me.[Airframe Number] & "'"
Debug.Print strSQL




Err_Select_Click:
    MsgBox Err.Description
    Resume Exit_Select_Click
   
Exit_Select_Click:
    Exit Sub

End Sub

Having done that, when I clicked the button, I got this:

View attachment 84681
The Microsoft Access window could not be resized, and I could not see anything in it, but on clicking "OK", I got this:
View attachment 84682
which in a perverse sort of way is right, because the code *should* work properly, I can't see syntax errors (or perhaps I'm not looking in the right place)

Try replacing Debug.Print strSQL with MsgBox strSQL.

 

[Isaac]

A bit "off" of the immediate vein of troubleshooting, but didn't you want to "toggle" the Complete column? Toggle meaning not just set it to False, but rather set it to the opposite of whatever it is.

I scanned the previous posts and didn't readily see this suggestion, so am adding it:
If so, you should probably include with an IIF statement:

update [tablename] set [fieldname]=iif([fieldname]=true, false, true) where [otherfield]=whatever

You might need to adjust the expression even a bit more if your yes/no column allows triple state (I can't even remember if they do, because I never use this type of column), and then it would depend on your preference/business logic for performing the 'toggle'.

(Generic example).

 

[Gasman]

@Isaac
The O/P's logic does work. I've tested it. Please see post #8

 

[Jim54]

OK, my thanks for the flurry of replies and help. I shall try to reply fully to each of you:
@Gasman I did as you suggested, and the code nd the "Immediate" window is shown below:

so given that the airframe selection was actually for DV155, that looks like its at least got that bit right.
@theDBguy : I did as you suggested ("Try replacing Debug.Print strSQL with MsgBox strSQL.") and got the following:

which looks right....doesn't it?

@Isaac , you wrote " didn't you want to "toggle" the Complete column? ": The purpose of the "Complete?" column is simply to allow the user to flag when he has completed each individual aircraft entry without having to go into each aircraft's full record. The idea is that when the user is happy that he has gathered a complete record for a particular 'Frame, he will toggle the "Complete?" field, which will then show as a tick box in a report containing just the 'Frame number and the tickbox for all 'Frame numbers. That way, the user can just toggle "Complete?" for selected, individual 'Frames from a small, tidy control form (the other way to control the "Complete?" field is via a massive form that holds 'Frame information, Aircrew information, notes and is not very convenient)

Guys, I really appreciate your help so far... you guys have taught me stuff I didn't (should've!) known or have forgotten, so great thanks and appreciation to all!

Jim

 

[theDBguy]

OK, my thanks for the flurry of replies and help. I shall try to reply fully to each of you:
@Gasman I did as you suggested, and the code nd the "Immediate" window is shown below:
View attachment 84701

so given that the airframe selection was actually for DV155, that looks like its at least got that bit right.
@theDBguy : I did as you suggested ("Try replacing Debug.Print strSQL with MsgBox strSQL.") and got the following:

View attachment 84702
which looks right....doesn't it?

@Isaac , you wrote " didn't you want to "toggle" the Complete column? ": The purpose of the "Complete?" column is simply to allow the user to flag when he has completed each individual aircraft entry without having to go into each aircraft's full record. The idea is that when the user is happy that he has gathered a complete record for a particular 'Frame, he will toggle the "Complete?" field, which will then show as a tick box in a report containing just the 'Frame number and the tickbox for all 'Frame numbers. That way, the user can just toggle "Complete?" for selected, individual 'Frames from a small, tidy control form (the other way to control the "Complete?" field is via a massive form that holds 'Frame information, Aircrew information, notes and is not very convenient)

Guys, I really appreciate your help so far... you guys have taught me stuff I didn't (should've!) known or have forgotten, so great thanks and appreciation to all!

Jim

Hi Jim. So now, your next step could be, to copy that SQL statement into the clipboard. Then, create a new Query by going to the Ribbon and click Create > Query Design. Close the Table List window and click on the View button on the Ribbon and select SQL. Now, paste the content of the clipboard into the query designer and Run it. Tell us what error message you get this time.

 

[Isaac]

@Isaac
The O/P's logic does work. I've tested it. Please see post #8

Ahh. Gotcha. My bad.

 

[Gasman]

OK, I was thinking that there was no value for the airframe number
At least we know the sql is correct, well as far as I can see, and I've done the same in my test DB, just that the field does not have a ? in it's name, but other than that, it is the same AFAIK?

I am out of ideas, sorry.

All I can think of now is paste that full sentence into a query sql window and see if it runs there.?
However one more question. Do you have a query called Aircraft, and if so how many fields are in it.?

 

[Jim54]

OK, I did as suggested in post #17 and this is what I got:

Something odd going on here: The statement starts "UPDATE", but the error is that the statement doesn't start with "UPDATE"....Huh???

The syntax is apparently right, as I don't get a "compile error"....

@Gasman, in answer to your question " Do you have a query called Aircraft, and if so how many fields are in it.? " I have attached a screen shot of the Queries, from which you can see there is no Query called "Aircraft" (just a table whose fields are shown in post #6), though there is a query called "Aircraft Query" (as I look back on this, I wonder what I was thinking of when I broke all the rules of good practice when I wrote it. It must offend all your sensibilities, for which I am deeply sorry)