question about queries

[Guest]

I have a procedure that I am doing. In form veiw I have used a query so I
can see and edit information. I have criteria so I am veiwing the
information that pertains to me. However when I am done I click a command
button to enter the date but I am failing somewhere this is my command button

Private Sub Command7_Click()
Me.Refresh
CurrentDb.Execute "UPDATE Cert SET [Date Approved] = Date()", dbFailOnError

How do I specify what records in my query that I want the date to appear.
Right now it puts the date in everyone . So right now lets say, I enter
monthly billing forms so child care providers can get paid. When they are
approved for payment we click the command button and that authoizes them for
payment. Since I do things in batches of 30 bills at a time. This code does
not allow me to do multiple batches. Right now Each batch needs to be
approved before I can start on the next one. I label each bill with a batch
number. When I veiw them I choose my batch number, If I feel the need to
hold one that is what the refresh is for. My original query has the critera,
Hold-False, and also filters by the batch number. So my question is, how can
I modify this to where I can do multiple batches at a time, without approving
all of them at one time?

Thanks
Chey

 

[Albert D. Kallal]

How do I specify what records in my query that I want the date to appear.

Well, if you tell me what records you want, then I can tell you the criteria
you need!!

So, how do you define a batch, and how do you identify, or "know" what batch
you want to update?

 

[Guest]

I have an unbound box. I choose my batch number. It then filters the batch
number. In my query right now I have
TheMonth2
Batch Number
Vendor Number
Hold---False
Date Approved---Is null
Late-Late
Date Inputed---Is Not null

When I go to Certify them I only want to certify the ones that are Date
Inputed and the batch number. Those 2 fields are the key fields. So when I
certify it will not certify anything in any other batch. Only the current
batch. When I open the form for example I am working in batch 1, but batch
2,3,4,5 have bills logged in them and I hit the command button only batch one
from that day will be certified. I hope this make sense.
Thanks
Chey

 

[Albert D. Kallal]

Great!.....

So, then, you siply simply restrict the update to the given batch numer.

By the way, often, I create a table called "batch" records. If you look at
the LAST screen shot here:
http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

You can very well see that I created a table for batches, and then things
like who completed the batch, and even the the date of the batch can be
saved this wasy.

I have an unbound box. I choose my batch number. It then filters the
batch
number. In my query right now I have
TheMonth2
Batch Number
Vendor Number
Hold---False
Date Approved---Is null
Late-Late
Date Inputed---Is Not null

dim strSql as tring

"UPDATE Cert SET [Date Approved] = #" & format(Date(),"mm/dd/yyyy") & "#" &
_
" and [Batch Number] = " & me.UnboundBoxBatchNumber


CurrentDb.Execute strSql, dbFailOnError


Note while the previous sql used date approved = date(), in theory, you need
to ALWAYS use USA format, and since your computer might have this setting
changed, your update might fail, or even worse update the wrong records.

I also assumed that batch number is a number type field, if it is not, then
the above needs to surrounded the unbound box batch number with quotes.

Also, if you really want save some pain, gray hairs, furstation, and some
suffering, try to avoid spaces in field names in the future....

 

[Albert D. Kallal]

just looking....

dim strSql as tring

"UPDATE Cert SET [Date Approved] = #" & format(Date(),"mm/dd/yyyy") & "#" &
_
" and [Batch Number] = " & me.UnboundBoxBatchNumber


CurrentDb.Execute strSql, dbFailOnError

The above should have been:

dim strSql as string

strSql = "UPDATE Cert SET [Date Approved] = #" & _
format(Date(),"mm/dd/yyyy") & "#" & _
" and [Batch Number] = " & me.UnboundBoxBatchNumber

CurrentDb.Execute strSql, dbFailOnError

 

[Guest]

something I forget to mention. I have a form with an unbound box. I use
choose my batch number and then it opens the new form, which filters that
batch number. On my new form, I have the filter batch number in the header.
I then have a subform that has the information to be approved. Will this
code still work? I tried pasting what you gave me and change the unbound
name, that didn't work for me.
Thanks for your help.
chey

 

[Albert D. Kallal]

Sure, it should still work.

You don't mention which form you run the update from???

if from the first form, then:

dim strSql as string

strSql = "UPDATE Cert SET [Date Approved] = #" & _
format(Date(),"mm/dd/yyyy") & "#" & _
" and [Batch Number] = " & me.UnboundBoxBatchNumber

CurrentDb.Execute strSql, dbFailOnError

If from the 2nd form, then use:

" and [Batch Number] = " &
forms!NameOfFirstForm!NameofUnboundBoxBatchNumberTextboxOnForm

as mentioned, if batch number is a text type field, then the above needs to
surround the value with quotes.

 

[Guest]

Hello,
I still need a little bit of help.
I acually run my update off of a subform
My button is on the main form which updates the subform. However both
queries are the same for both forms.
Private Sub Command17_Click()
Dim strSql As String
strSql = "UPDATE Cert SET [Date Approved] = #" & _
Format(Date, "mm/dd/yyyy") & "#" & " and [Batch Number] = "
& _
Forms![Approved for Payment]![Batch Number]
End Sub

I also thought of something else. It needs to only update that batch number
and not the ones on hold. When I click the button nothing happens.
Thanks

 

[Albert D. Kallal]

Hello,
I still need a little bit of help.
I acually run my update off of a subform
My button is on the main form which updates the subform. However both
queries are the same for both forms.

Ok, so, the buttion (and therefore the code) is on the MAIN form, and NOT
the sub-form.
Therefore, the code is on the main form, and we run the udpate on the main
form.

So, the code you have (that we will run) is on the MAIN form.

Private Sub Command17_Click()
Dim strSql As String
strSql = "UPDATE Cert SET [Date Approved] = #" & _
Format(Date, "mm/dd/yyyy") & "#" & _
" and [Batch Number] = " me![Batch Number]

currentdb.execute strSql


End Sub
Note how in your code you did not have
currentdb.execute strSql

strSql = "hello, how are you"
msgbox strSql

If you were to run the above two lines of code, you would see a msgbox with

"hello, how are you"

So, simply stuffing some text into "strSql" does not somehow magacially run
the sql, you have to actually execute the string (text) with a command.

I also thought of something else. It needs to only update that batch
number
and not the ones on hold.

You simply then need to restrict the query to the records not on hold (what
field, and how you do that, I can't know, but you most certainly can add to
the conditions, *exmample*

strSql = "UPDATE Cert SET [Date Approved] = #" & _
Format(Date, "mm/dd/yyyy") & "#" & _
" and [Batch Number] = " me![Batch Number] & _
" and FieldOnHold = False"

Again, the above is just an example, and I have actually no idea how, or
what field is used to designate a record on hold.

Note also, since you are quite new to this, make a backup copy. If you type
in the wrong criteria here, you could wind up modify the WHOLE FILE to some
wrong number. et:

update cert set [Batch number] = 0

The above would blow out all your batch numbers. So, if you are new to this,
use EXTREME caution, as those sql commands operate ON WHOLE TABLES, and in a
flash, you can trash your data faster then you can blink you eyes (so, make
a back up before you try ANY type of update).

Again, take note, and use CAUTION with those update commands.

When I click the button nothing happens.

yes, and that tells me you are new to this, and thus you need to copy/backup
your data BEFORE you try this new stuff....

Also, you mention that the main form is bound to the same query as the
sub-form. Are those fields edited on the MAIN form (or what we call dirty)
BEFORE you run the query? (this is important). If yes to this answer, then
you need to force a disk write BEFORE you run the update code.
You can place a:
me.refresh
right before you execute the update....