Update table silently

Hi I've got a function shown below which increments a number and stores
it back in a table in Access.

This works fine except I would like it to update the number silently
and not ask the user "You are about to update 1 row. Once you click yes
you cant use the Undo command to reverse the changes". This number
should be stored without the user even know about it. Is this at all
possible?

Function IncrementLastUsedNumber()

Dim myNumber As Integer
Dim strSQL As String

myNumber = DMax("[LastUsedNumber]", "tableLastUsedNumber") + 1
strSQL = "UPDATE tableLastUsedNumber SET LastUsedNumber = " &
myNumber & " "
DoCmd.RunSQL (strSQL)

End Function

Many thanks!

re: Update table silently

Found my own answer call off the search. Cheers

Its docmd.setwarnings false

Before the code then docmd.setwarnings true after it.

ROCK ON!

re: Update table silently

Glad you found it, I was wondering myself.

re: Update table silently

What do you do at your work? (I'm at work too lol)

re: Update table silently

Just started a new job working sort of in IT (but not quite am in a
town planning department and they need me to be inhouse IT support so
they dont have to wait ages for the main IT department). It's been a
bit boring up till now only installing software and trouble shooting.
Was a network manager in previous job. But that didnt pay enough.

Finished university a coupke of years ago.Studied Computing Wish i was
back there- lol!

re: Update table silently

Beacher if i need this to update ONLY when the form has been
completed.do you know where i should call it from?

Im trying to create a customised auto number - in the format "C -
00001"
I was gonna use auto number but if you start filling out a form then
cancel then the autonumber will already have been created.
I want each new record to go in sequence C-00001, C-00002, C-00003
etc...
I know how to append this number thats incrementing to the end of
c-0000 by using

templateReceiptNo = "C - 00000"

Form_form1.txtReceiptNo.SetFocus

addToEnd = DMax("[mycounter]", "tablecount") + 1 \\Note am going
to use my new function here
templateReceiptNoLength = Len(templateReceiptNo)
addToEndLength = Len(addToEnd)
newLength = templateReceiptNoLength - addToEndLength
temporaryReceiptNo = Left(templateReceiptNo, newLength)
newReceiptNo = temporaryReceiptNo & addToEnd

I just need to know at what point should i call this to insure that the
number is not incremented and stored for the new record unless the form
has been totally filled out.

Cheers

re: Update table silently

simonmarkjones@gmail.com wrote:
[color=blue]
> Found my own answer call off the search. Cheers
>
> Its docmd.setwarnings false
>
> Before the code then docmd.setwarnings true after it.
>
> ROCK ON![/color]

I would avoid this unless you absolutely positively sure that the update
statement will NEVER ever fail. If it does, you might be stuck with
warnings set to off and have lost your paddle....

Try this instead:

Function IncrementLastUsedNumber()

Dim myNumber As Integer
Dim strSQL As String

myNumber = DMax("[LastUsedNumber]", "tableLastUsedNumber") + 1
strSQL = "UPDATE tableLastUsedNumber SET LastUsedNumber = " &
myNumber & " "

Access.dbcurrent.execute strSQL dbFailOnError

End Function

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Update table silently

I'm going into my last year of school, my job (as a programmer) is a
paid 4 month coop :) I'm loving it :D.. Anyways, why not just have an
if statement checking if anything is null, and if they arent then call
your function =)

re: Update table silently

Thanks all on your help.

Need another cup of coffee b4 home time. Yipeeeeeeeee!

re: Update table silently

Home time! I still got 5 hours :(

re: Update table silently

Im in England Uk. So its 4.40pm here.

I've got my IF
If ((Form_form1.txtReceiptNo.Text = "") Or
(Form_form1.PaymentMethod.Text = "") Or (Form_form1.PaymentAmount.Text
= "") Or (Form_form1.CostCode.Text = "") Or (Form_form1.Address.Text =
"") Or (Form_form1.DateInserted.Text = "") Or (Form_form1.lbName.Text =
"")) Then ....

But when i call it it says "You cant reference a property or method
unless the control has the focus. Do i really have to code
Form_form1.Address.Focus etc for each one just to check them??

Learned loads today. Cheers

re: Update table silently

Try .Value instead of .text if that doesent work im not sure... all my
experience is in .NET and java.. not much with vba at all.. just
started using it at this job

re: Update table silently

simonmarkjones@gmail.com wrote:[color=blue]
> Im in England Uk. So its 4.40pm here.
>
> I've got my IF
> If ((Form_form1.txtReceiptNo.Text = "") Or
> (Form_form1.PaymentMethod.Text = "") Or (Form_form1.PaymentAmount.Text
> = "") Or (Form_form1.CostCode.Text = "") Or (Form_form1.Address.Text =
> "") Or (Form_form1.DateInserted.Text = "") Or (Form_form1.lbName.Text =
> "")) Then ....[/color]

Drop the .text. The text property refers to the value of the control
before the change is confirmed and is only available when the control
has the focus, ie your cursor is in the text box (if it's a text box).

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

re: Update table silently

Even easier than disabling and re-enabling warnings, instead of:
DoCmd.RunSQL (strSQL)

....use:
CurrentDb.Execute strSQL

<simonmarkjones@gmail.com> wrote in message
news:1116425823.533376.252810@o13g2000cwo.googlegr oups.com...[color=blue]
> Found my own answer call off the search. Cheers
>
> Its docmd.setwarnings false
>
> Before the code then docmd.setwarnings true after it.
>
> ROCK ON!
>[/color]