Running sql from command button

[BFish via AccessMonster.com]

I have been working on this for two days looking at "help" and other postings
and just not getting a handle on it.

I'm using a command button on click event to check for a checkbox = true, if
not then run a sql to reset to true. With reading other postings I've gotten
to this point:

Dim strSQL As String
Set db = CurrentDb

strSQL = "UPDATE tblPhone SET PrimaryPhone = -1 WHERE"
strSQL = strSQL & " PhoneID=" & Forms("frmAddPhone").Controls("txtPhoneID")

db.Execute strSQL, dbFailOnError
Set db = Nothing

Using debug dbFailOnError is 128 and strSQL with have correct PhoneID value.

Any help as always is greatly aprreciated.

Thanks,
Bill Fischer

 

[Marshall Barton]

I have been working on this for two days looking at "help" and other postings
and just not getting a handle on it.

I'm using a command button on click event to check for a checkbox = true, if
not then run a sql to reset to true. With reading other postings I've gotten
to this point:

Dim strSQL As String
Set db = CurrentDb

strSQL = "UPDATE tblPhone SET PrimaryPhone = -1 WHERE"
strSQL = strSQL & " PhoneID=" & Forms("frmAddPhone").Controls("txtPhoneID")

db.Execute strSQL, dbFailOnError
Set db = Nothing

Using debug dbFailOnError is 128 and strSQL with have correct PhoneID value.

I don't remember that error number, but check the data type
of the phoneID field in the table. If it's a Text field,
you'll need quotes around the value:

.... & " PhoneID='" & Forms!frmAddPhone.txtPhoneID & "' "

 

[Guest]

strSQL = strSQL & " PhoneID = " & Me.txtPhoneID & ""
You're missing some single quotes
strSQL = strSQL & " PhoneID = '" & Me.txtPhoneID & "'"

Hi BFish,

replace;
strSQL = strSQL & " PhoneID=" & Forms("frmAddPhone").Controls("txtPhoneID")

with;
strSQL = strSQL & " PhoneID = " & Me.txtPhoneID & ""

provided txtPhoneID is a number and the command button is on the same form
as the field txtPhoneID, else use;
[Forms]![frmAddPhone].[txtPhoneID]

hope this helps,

TonyT..

I have been working on this for two days looking at "help" and other postings
and just not getting a handle on it.

I'm using a command button on click event to check for a checkbox = true, if
not then run a sql to reset to true. With reading other postings I've gotten
to this point:

Dim strSQL As String
Set db = CurrentDb

strSQL = "UPDATE tblPhone SET PrimaryPhone = -1 WHERE"
strSQL = strSQL & " PhoneID=" & Forms("frmAddPhone").Controls("txtPhoneID")

db.Execute strSQL, dbFailOnError
Set db = Nothing

Using debug dbFailOnError is 128 and strSQL with have correct PhoneID value.

Any help as always is greatly aprreciated.

Thanks,
Bill Fischer

 

[Guest]

Hi BFish,

replace;
strSQL = strSQL & " PhoneID=" & Forms("frmAddPhone").Controls("txtPhoneID")

with;
strSQL = strSQL & " PhoneID = " & Me.txtPhoneID & ""

provided txtPhoneID is a number and the command button is on the same form
as the field txtPhoneID, else use;
[Forms]![frmAddPhone].[txtPhoneID]

hope this helps,

TonyT..

 

[BFish via AccessMonster.com]

Thanks for the quick reply Marshall,

PhoneID is Autonumber.
Since my post I've put in my code the Debug.Print strSQL function, then have
taken the sql value pasted that into a sql statement query, runs correctly
with updating the check box.

Of what I think is great interest and no not what to do if needed the db
prints as nothing for value.

Dim db As Database
Dim strSQL As String

Set db = DBEngine(0)(0)
Set db = CurrentDb()

strSQL CODE HERE

db.Execute strSQL, dbFailOnError
Set db = Nothing

I think the above references are correct, I have toggled between the two Set
db statements but of course have only one enable at a time.

Could this be a DAO problem

Thanks again,
Bill

 

[BFish via AccessMonster.com]

Hi TonyT,

I tried changing the string, but alas no change on out come. I did paste the
sql value from the immediate window for both your change and previous version
into a sql query, and both turned check box value to true.

Just between last paragraph and this one, did some more changing.
Changed strSQL with:
strSQL = strSQL & " PhoneID=" & Forms("frmAddPhone").Controls("txtPhoneID") -
1

Note the -1, one less autonumber ID

to pickup the last prior saved record, emphasis on saved, the value for the
checkbox for that ID DID change to true.

More information this command button is on the form frmAddPhone which when
called is in dialog mode as a popup. Correct me if I am wrong will dialog
mode keep a new record saving to table until the form closes.

Is there code to perform the save then run the sql to check for true value
before the form closes or should this code be in the "On Close" event.

Thanks again,

Bill

 

[Marshall Barton]

I tried changing the string, but alas no change on out come. I did paste the
sql value from the immediate window for both your change and previous version
into a sql query, and both turned check box value to true.

Just between last paragraph and this one, did some more changing.
Changed strSQL with:
strSQL = strSQL & " PhoneID=" & Forms("frmAddPhone").Controls("txtPhoneID") -
1

Note the -1, one less autonumber ID

to pickup the last prior saved record, emphasis on saved, the value for the
checkbox for that ID DID change to true.

More information this command button is on the form frmAddPhone which when
called is in dialog mode as a popup. Correct me if I am wrong will dialog
mode keep a new record saving to table until the form closes.

Is there code to perform the save then run the sql to check for true value
before the form closes or should this code be in the "On Close" event.

If the record you are trying to modify has not been saved,
then why use sql to edit the value???? Can't you just set
the value directly on the form:

Forms!theotherform.thecheckbox = True

OTOH, why are you using a dialog box in the middle of
entering a new record??? Seems like it's just getting in
the way?

You can force a record to be saved by using:

If Forms!theotherform.Dirty _
Then Forms!theotherform.Dirty=False

 

[BFish via AccessMonster.com]

Very good questions Marshall, and thanks for your reply again.

Let me lay out what this popup is accomplishing for the db, and please if you
have ideas on a better approach please let me know.

The user starts on the Invoice Entry Form to add new invoices starting by
entering phone number, this db must capture all phone numbers for any
business.

This frmAddPhone (popup) is for any phone # "not in the list" giving the user
the option to add the phone number as a additional # to a existing business
or creating a new businessID. When phone # is added we need to know if this
is the Primary phone for the business (the checkbox) for other uses in the db
and of course there can only be one primary phone #. Default setting for
tblPhone.primaryphone (checkbox is false).

My code (taking it out of the users hands for the new business) counts number
of phoneID's if only one "after record save" then that is the primary phone,
but if multiple phone numbers exist a msgbox appears to prompt if the new
phone phone # is the new default or to leave current primaryphone as primary.


I ended using a DoCmd.RunCommand acCmdSaveRecord to continue with my code
work, before you had posted your latest reply, all seems to be working.
Again if you see any possible improvement please let me know.

I am but an amateur in Access programming, for that matter any programming,
but love learning what and how to accomplish the end need. That's what is so
great about this news group and persons like yourself taking the time to post.


Thanks again,
Bill

 

[Marshall Barton]

Very good questions Marshall, and thanks for your reply again.

Let me lay out what this popup is accomplishing for the db, and please if you
have ideas on a better approach please let me know.

The user starts on the Invoice Entry Form to add new invoices starting by
entering phone number, this db must capture all phone numbers for any
business.

This frmAddPhone (popup) is for any phone # "not in the list" giving the user
the option to add the phone number as a additional # to a existing business
or creating a new businessID. When phone # is added we need to know if this
is the Primary phone for the business (the checkbox) for other uses in the db
and of course there can only be one primary phone #. Default setting for
tblPhone.primaryphone (checkbox is false).

My code (taking it out of the users hands for the new business) counts number
of phoneID's if only one "after record save" then that is the primary phone,
but if multiple phone numbers exist a msgbox appears to prompt if the new
phone phone # is the new default or to leave current primaryphone as primary.

I ended using a DoCmd.RunCommand acCmdSaveRecord to continue with my code
work, before you had posted your latest reply, all seems to be working.
Again if you see any possible improvement please let me know.

I am but an amateur in Access programming, for that matter any programming,
but love learning what and how to accomplish the end need. That's what is so
great about this news group and persons like yourself taking the time to post.

I was afraid that's what you were doing. Technically, that
is an unnormalized table design and you are struggling with
the consequences. (The [primary phone] field is the
equivalent of saving a sum of a group of records).

I don't have a "best way" to deal with this situation, but I
would seriously consider saving the primary phone id in the
customer table. It seems like the primary phone is an
attribute of the customer entity more than the phone number
entity. At least this would eliminate the need for updating
a bunch of records in the phone numbers table.

As for saving a record, I try to avoid using RunCommand
methods when there is another way. The problem with
RunCommand methods is that they have no way for you to
specify which object you want to affect. They operate on
the active object, which can jump around unpredictably (user
clicking wildly, a timer event, etc). While it is rather
obscure, the Dirty technique can only affect the specified
form object whether it is active or not.

 

[BFish via AccessMonster.com]

Yes I was not greatly comfortable with this layout (unnormalized table design)
but decided this may have been my best option considering a on the fly
primary phone change was a needed option. I hadn't thought of saving only
the primary phone ID in the business table and will reevaluate.

On your suggestion for the present layout I did change the save record code
to the If Form Dirty in your previous post.

Thank you for your insight.

Bill

Very good questions Marshall, and thanks for your reply again.

[quoted text clipped - 24 lines]

but love learning what and how to accomplish the end need. That's what is so
great about this news group and persons like yourself taking the time to post.

I was afraid that's what you were doing. Technically, that
is an unnormalized table design and you are struggling with
the consequences. (The [primary phone] field is the
equivalent of saving a sum of a group of records).

I don't have a "best way" to deal with this situation, but I
would seriously consider saving the primary phone id in the
customer table. It seems like the primary phone is an
attribute of the customer entity more than the phone number
entity. At least this would eliminate the need for updating
a bunch of records in the phone numbers table.

As for saving a record, I try to avoid using RunCommand
methods when there is another way. The problem with
RunCommand methods is that they have no way for you to
specify which object you want to affect. They operate on
the active object, which can jump around unpredictably (user
clicking wildly, a timer event, etc). While it is rather
obscure, the Dirty technique can only affect the specified
form object whether it is active or not.