Programming a button on a Form

[Gasman]

No, post the hardcoded version from the immediate window as it was printed into the query window.

Just a theory. I tried updating a query name and got that expected parameters error.

 

[theDBguy]

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

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)

View attachment 84704

Hi. I think you got that error because your SQL statement starts with a quote. Try to copy and paste the exact text from the Immediate Window or the Message Box (not the one from the VBA code).

 

[Jim54]

OK, something sensible here...I did as @Gasman suggested, and this is what I got back:

So although there is a "Completed?" field in the "Airframe" table, the query apparently can't find it, despite having the right "Airframe Number", so asks for the value. As far as I can make out, the "missing parameter" of the original error is the lack of a "Completed?" value.

Is this likely to be something to do with the fact that I added the "Completed?" field long after the table and most of the content was created, and could this be because the yes/no status of the field is inclear. I know the value should be either -1 or 0, but if I just created the field, do I need to initialise it somehow, to drive it to one state or another? or does it default to a predefined status?

 

[theDBguy]

OK, something sensible here...I did as @Gasman suggested, and this is what I got back:

View attachment 84705
So although there is a "Completed?" field in the "Airframe" table, the query apparently can't find it, despite having the right "Airframe Number", so asks for the value. As far as I can make out, the "missing parameter" of the original error is the lack of a "Completed?" value.

Is this likely to be something to do with the fact that I added the "Completed?" field long after the table and most of the content was created, and could this be because the yes/no status of the field is inclear. I know the value should be either -1 or 0, but if I just created the field, do I need to initialise it somehow, to drive it to one state or another? or does it default to a predefined status?

Hi. How about creating another query with this SQL statement?

SELECT [Airfram Number], [Completed?] FROM Aircraft

Does it run?

Regarding your question about Yes/No field, is your data stored in SQL Server? If not, then no, you don't have to initialize the field. The default value is No/False/0.

 

[Jim54]

@theDBguy : I did as you suggested, and the result looked like the screenshot below, so I gave it "0" for "Completed?"

and got the output below, which looks reasonable, showing all 'Frame numbers with a "Completed?" value of 0.

However, I had to tell the query the status of "Completed?", it couldn't find it itself, so I went back to the original query, which had also asked for the "Completed?" value, and it throws an error which indicates the field is not updateable....but it is if accessed from a different form....do I have a capabilities/permissions issue here?

 

[Gasman]

Is the field actually called completed? And that is not the caption?

 

[Jim54]

@Gasman : Yes, the field is called "Completed?", as you can see from the table headings :

But now I'm concerned that I don't know the difference between field name and caption. There is a chance that I'm wrong (always a chance!) but the field "Completed" works in other reports, etc, so I hope I'm right.

 

[Gasman]

Completed and completed? Are different ????

 

[Jim54]

Completed and completed? Are different ????

They are different, but I've used "Completed?" throughout...unles you have seen somewhere I haven't

 

[Gasman]

No, but I am not sure if you are displaying captions? One thing we know is that access does.not recognize it?
go into design mode for the table and check. I'm off to bed now,so good luck

 

[jdraw]

How about posting a copy of the database. Prefer ZIP format. And remove anything private/confidential before zipping.

 

[Jim54]

@Gasman ....Oh, good grief! You have nailed it. The field name in design view is "Complete", but I was referring to it as "Complete?" You will read this tomorrow, as you have gone to bed, but your parting action was to fix the problem! Sleep well, you deserve it.

I changed the original query to use the right name and Bingo! it works perfectly.

There are several (fairly critical) names I'd like to call myself, as I'm sure, would you!

Thanks and Kudos are due to Gasman, theDBguy and Isaac, you guys have been great, and I owe you all! (And thanks to jdraw for being brave enough to volunteer to get involved in the mess I created!)

 

[theDBguy]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[jdraw]

I'm sure all were happy to participate/share thoughts.
An old tip is Do NOT include embedded spaces nor special characters** in field names.

** non-alphanumeric (eg. !@#$%^&*?+=_-......)

 

[Jim54]

I'm sure all were happy to participate/share thoughts.
An old tip is Do NOT include embedded spaces nor special characters** in field names.

** non-alphanumeric (eg. !@#$%^&*?+=_-......)

It's a very good point you make, my only defence being that I wrote this DB originally back in the mid '90s, and was (badly) teaching myself Access at the time; best practices were abandoned in favour of anything that worked...I didn't expect the DB to still be operational this far on, and I'm now wondering if it may be wise to rewrite it with best practices in mind. I don't use Access at all professionally, I build networks, thats why I'm so bad at it and so grateful to this forum and its participants. Thanks!

Jim

 

[arnelgp]

basically, you need to go back to your Table in design view
and check the Real fieldname (to use in your query and not
it's Caption or Display name).

 

[Gasman]

@Gasman ....Oh, good grief! You have nailed it. The field name in design view is "Complete", but I was referring to it as "Complete?" You will read this tomorrow, as you have gone to bed, but your parting action was to fix the problem! Sleep well, you deserve it.

I changed the original query to use the right name and Bingo! it works perfectly.

There are several (fairly critical) names I'd like to call myself, as I'm sure, would you!

Thanks and Kudos are due to Gasman, theDBguy and Isaac, you guys have been great, and I owe you all! (And thanks to jdraw for being brave enough to volunteer to get involved in the mess I created!)

In fact you were showing Completed? in your pics, but I was not sure if captions were shown on table view? Obvioulsy they are and I have just tested on my Test DB. I could see this being what I call a 'silly' error. Hard to track down, and when you do, it's Doh!.

We got there in the end with the bonus, you have learnt a few debugging techniques. Well Done.