By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,454 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

VBA- Help!

P: n/a
Hal
Hi,

Please see http://webmonky.myby.co.uk/problem.JPG

I have 1 Text Box in a form (ms access), one for an Order # .I currently
have the form working so when I enter (or scan) an order number, that order
number (which is a field in a record) is marked as DESPACTHED in a Tickbox
Field under that record.
Here is the code..

CurrentDb.Execute "Update tblDownload Set DESPATCHED = True Where ID = " &
Me.txtOrderNumber & ";", dbFailOnError
Me.StatusListBox.AddItem "ORDER # " & Me.txtOrderNumber & " MARKED AS
DESPATCHED"
(Thanks Joe!)

....Now what I need is for it to continue doing that, but to ALSO add a
Tracking Number (using another text box) to the same record as the Order #.

Any suggestions would be excellent, I've had a shot at it and I just can't
get my head around it.

E-mails welcome at bl*************@blueyonder.co.uk

Many Thanks,
Nick
Nov 13 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

"Hal" <a@b.com> wrote in message
news:%F******************@text.news.blueyonder.co. uk...
Hi,

Please see http://webmonky.myby.co.uk/problem.JPG

I have 1 Text Box in a form (ms access), one for an Order # .I currently
have the form working so when I enter (or scan) an order number, that order number (which is a field in a record) is marked as DESPACTHED in a Tickbox
Field under that record.
Here is the code..

CurrentDb.Execute "Update tblDownload Set DESPATCHED = True Where ID = " &
Me.txtOrderNumber & ";", dbFailOnError
Me.StatusListBox.AddItem "ORDER # " & Me.txtOrderNumber & " MARKED AS
DESPATCHED"
(Thanks Joe!)

...Now what I need is for it to continue doing that, but to ALSO add a
Tracking Number (using another text box) to the same record as the Order #.
Any suggestions would be excellent, I've had a shot at it and I just can't
get my head around it.

E-mails welcome at bl*************@blueyonder.co.uk

Many Thanks,
Nick


Hi Nick

Something like
"Update tblDownload Set DESPATCHED = True, TRACKING_NUMBER = " &
Me.txtTrackingNumber & " Where ID = " & Me.txtOrderNumber & ";",
dbFailOnError

Again, if tracking number is a text field you will have to insert quotation
marks
around Me.txtTrackingNumber in the sql string.
Regards - Joe
Nov 13 '05 #2

P: n/a
Hal
"Joe Black" <jo**********@hotmail.com> wrote in message
news:XT*******************@news.xtra.co.nz...

"Hal" <a@b.com> wrote in message
news:%F******************@text.news.blueyonder.co. uk...
Hi,

Please see http://webmonky.myby.co.uk/problem.JPG

I have 1 Text Box in a form (ms access), one for an Order # .I currently
have the form working so when I enter (or scan) an order number, that order
number (which is a field in a record) is marked as DESPACTHED in a Tickbox Field under that record.
Here is the code..

CurrentDb.Execute "Update tblDownload Set DESPATCHED = True Where ID = " & Me.txtOrderNumber & ";", dbFailOnError
Me.StatusListBox.AddItem "ORDER # " & Me.txtOrderNumber & " MARKED AS
DESPATCHED"
(Thanks Joe!)

...Now what I need is for it to continue doing that, but to ALSO add a
Tracking Number (using another text box) to the same record as the Order

#.

Any suggestions would be excellent, I've had a shot at it and I just can't get my head around it.

E-mails welcome at bl*************@blueyonder.co.uk

Many Thanks,
Nick


Hi Nick

Something like
"Update tblDownload Set DESPATCHED = True, TRACKING_NUMBER = " &
Me.txtTrackingNumber & " Where ID = " & Me.txtOrderNumber & ";",
dbFailOnError

Again, if tracking number is a text field you will have to insert

quotation marks
around Me.txtTrackingNumber in the sql string.
Regards - Joe

Hey Joe thanks again!

I'm using this now...(the tracking number has letters in it so I'm using the
" " marks on Me.txtTracking)

CurrentDb.Execute "Update tblDownload Set DESPATCHED = True, TRACKING = " &
"Me.txtTracking" & " Where ID = " & Me.txtOrderNumber & ";", dbFailOnError

But I keep getting Too Few Parameters. Expected 1 (error 3061), any ideas?

Cheers,
Nick
Nov 13 '05 #3

P: n/a
I'm using this now...(the tracking number has letters in it so I'm using the
" " marks on Me.txtTracking)

CurrentDb.Execute "Update tblDownload Set DESPATCHED = True, TRACKING = " &
"Me.txtTracking" & " Where ID = " & Me.txtOrderNumber & ";", dbFailOnError

But I keep getting Too Few Parameters. Expected 1 (error 3061), any ideas?


What will make it plainer for you to see where you are going wrong in
this and future exercises is to use the debug.print command.

In your procedure, dim a string variable at the top of the procedure,
then assign the sql statement to the string like so:

dim strSql as string

strSql = <insert the sql expression you're using in your execute
statement>

debug.print strSql

currentdb.execute strsql, dbfailonerror

The procedure will again fail at the execute statment. However, this
time, press ctrl-G and look at what is showing. This is exactly what
Access is trying to execute. Your problem will become immediately
apparant (I use 3 as a "pretend" order number):

Update tblDownload Set DESPATCHED = True, TRACKING = Me.txtTracking
Where ID = 3;

Your problem is that you have enclosed me.txttracking in double quotes.
Remove your quotes so that you have

"Update tblDownload Set DESPATCHED = True, TRACKING = " & Me.txtTracking
& " Where ID = " & Me.txtOrderNumber

And all will be well. You can later rem out the debug.print, once
you're satisifed your procedure is working as required. Also, you don't
need the semi-colon.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #4

P: n/a
Hal
"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cm**********@coranto.ucs.mun.ca...
I'm using this now...(the tracking number has letters in it so I'm using the " " marks on Me.txtTracking)

CurrentDb.Execute "Update tblDownload Set DESPATCHED = True, TRACKING = " & "Me.txtTracking" & " Where ID = " & Me.txtOrderNumber & ";", dbFailOnError
But I keep getting Too Few Parameters. Expected 1 (error 3061), any
ideas?
What will make it plainer for you to see where you are going wrong in
this and future exercises is to use the debug.print command.

In your procedure, dim a string variable at the top of the procedure,
then assign the sql statement to the string like so:

dim strSql as string

strSql = <insert the sql expression you're using in your execute
statement>

debug.print strSql

currentdb.execute strsql, dbfailonerror

The procedure will again fail at the execute statment. However, this
time, press ctrl-G and look at what is showing. This is exactly what
Access is trying to execute. Your problem will become immediately
apparant (I use 3 as a "pretend" order number):

Update tblDownload Set DESPATCHED = True, TRACKING = Me.txtTracking
Where ID = 3;

Your problem is that you have enclosed me.txttracking in double quotes.
Remove your quotes so that you have

"Update tblDownload Set DESPATCHED = True, TRACKING = " & Me.txtTracking
& " Where ID = " & Me.txtOrderNumber

And all will be well. You can later rem out the debug.print, once
you're satisifed your procedure is working as required. Also, you don't
need the semi-colon.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto


Very cool, many thanks for your help Joe and Tim!
Nov 13 '05 #5

P: n/a
Hal

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cm**********@coranto.ucs.mun.ca...
I'm using this now...(the tracking number has letters in it so I'm using the " " marks on Me.txtTracking)

CurrentDb.Execute "Update tblDownload Set DESPATCHED = True, TRACKING = " & "Me.txtTracking" & " Where ID = " & Me.txtOrderNumber & ";", dbFailOnError
But I keep getting Too Few Parameters. Expected 1 (error 3061), any
ideas?
What will make it plainer for you to see where you are going wrong in
this and future exercises is to use the debug.print command.

In your procedure, dim a string variable at the top of the procedure,
then assign the sql statement to the string like so:

dim strSql as string

strSql = <insert the sql expression you're using in your execute
statement>

debug.print strSql

currentdb.execute strsql, dbfailonerror

The procedure will again fail at the execute statment. However, this
time, press ctrl-G and look at what is showing. This is exactly what
Access is trying to execute. Your problem will become immediately
apparant (I use 3 as a "pretend" order number):

Update tblDownload Set DESPATCHED = True, TRACKING = Me.txtTracking
Where ID = 3;

Your problem is that you have enclosed me.txttracking in double quotes.
Remove your quotes so that you have

"Update tblDownload Set DESPATCHED = True, TRACKING = " & Me.txtTracking
& " Where ID = " & Me.txtOrderNumber

And all will be well. You can later rem out the debug.print, once
you're satisifed your procedure is working as required. Also, you don't
need the semi-colon.

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto


Hi Tim,

Sorry..seems to not be working with letters in the tracking number field.
The tracking number is in AB123456789CD format, I have done what you told
me to do with the sql string and now I keep getting is

"MS Jet Engine can't find the input table or query ", make sure it exists"

I can get it to work without declaring a sql string, but I could only use
numbers, not letters. I was told by Joe to put quotation marked around
Me.txtTracking if it was a text field (which it is)

Thanks, Nick
Nov 13 '05 #6

P: n/a
Hal wrote:
I can get it to work without declaring a sql string, but I could only use
numbers, not letters. I was told by Joe to put quotation marked around
Me.txtTracking if it was a text field (which it is)


What this means then is:

strsql = "Update tblDownload Set DESPATCHED = True, TRACKING = """ &
Me.txtTracking & """ Where ID = " & Me.txtOrderNumber

You have to properly delimit your data. Numbers don't require
delimiters, text requires double quotes. To show double quotes within
double quotes as above or if you are feeding this directly to your
execute statement are double quotes. Dates require the pound sign.

Sometimes double quotes can get confusing as to where to putn them and
it can be hard to keep trakc of them especially when you are just
starting out. An alternative to the above would be:

strsql = "Update tblDownload Set DESPATCHED = True, TRACKING = " &
chr(34) & Me.txtTracking & chr(34) & " Where ID = " & Me.txtOrderNumber

with chr(34) standing in for the double quotes. (chr(35) is for # BTW,
for dates).

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #7

P: n/a
Hal
"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cm*********@coranto.ucs.mun.ca...
Hal wrote:
I can get it to work without declaring a sql string, but I could only use numbers, not letters. I was told by Joe to put quotation marked around
Me.txtTracking if it was a text field (which it is)


What this means then is:

strsql = "Update tblDownload Set DESPATCHED = True, TRACKING = """ &
Me.txtTracking & """ Where ID = " & Me.txtOrderNumber

You have to properly delimit your data. Numbers don't require
delimiters, text requires double quotes. To show double quotes within
double quotes as above or if you are feeding this directly to your
execute statement are double quotes. Dates require the pound sign.

Sometimes double quotes can get confusing as to where to putn them and
it can be hard to keep trakc of them especially when you are just
starting out. An alternative to the above would be:

strsql = "Update tblDownload Set DESPATCHED = True, TRACKING = " &
chr(34) & Me.txtTracking & chr(34) & " Where ID = " & Me.txtOrderNumber

with chr(34) standing in for the double quotes. (chr(35) is for # BTW,
for dates).

--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto


Your right that it gets confusing, cheers mate, all done.

Thanks!

Nick
Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.