CHECKING FOR LAST DATE WORK DONE ON 1

[ve7epq]

Hi All. Best of the Season. I am working on a database for our WORK ORDER system. We have units come in randomly every 3 or 4 months. I have a field for todays date and for last_date. What I am trying to do...and logic defys me...is the following:

Serial_Number_AfterUpdate
once the s/n is input I need to check the [TYPE] and [Serial_NUMBER] of the current work order against my existing data in my query [qrySEARCH] If I find the match then I need to return the date from the previous work order to the open form. If not...I will return "Date" as the date of the last calibration/repair.

Help would be greatly appreciated

 

[ethorn10]

There might be an easier way, but you could define two boolean variables and set both to false, then check the current [TYPE] with every type in the query. I assume that both [TYPE] and [SERIAL_NUMBER] need to match, not just one. If you find a [TYPE] match, set the bool_type variable to true, else it remains false. If you get through the entire query and don't find a matching type, then return "Date". If bool_type is True, start searching the query again at the beginning. If you find a match, set bool_sn to true, else it remains false. If both bool_type and bool_sn are True, return last_date, otherwise return "Date".

Probably really wordy for something that can be done much faster/cleaner but I thought we'd get you started with that...and nobody else has helped yet.

 

[ve7epq]

I understand your suggestion. What I have been trying looks like the example in MSN database #199056. I want to return the Find.First on my Query. The query is in descending date order. That satisfies my need to find the most recent record.

What I dont understand is the function of RecordsetClone. Also I am working off a textbox [qrySEARCH].[SERIAL_NUMBER]

Private rst as DAO.Recordset
Const str1 = "[qrySEARCH].[TYPE]"
Const str2 = "[qrySEARCH].[SERIAL_NUMBER]

Private SERIAL_NUMBER_AFTER_UPDATE()
Dim strCriteria as String
Dim str as String

Set rst = Me.RecordsetClone

rst.FindFirst <Now I am not sure>

'as long as Me!TYPE only refers to the Data on open FORM that this code refers to

rst.FindFirst Where str1 = Me!TYPE and str2 = _
Me!SERIAL_NUMBER

If rst.NoMatch Then
LAST_DATE = Me!DATE

Else
<lost here too>
LAST_DATE = 'the date stored in the match on Recordset
'which I am not sure how to address
rst.close
End Sub

Hope this makes some sense

 

[ethorn10]

I'm not so sure about the RecordsetClone either. Maybe somebody else will jump in and help out...

 

[kphu]

Hey guys,

rst.findfirst is basically the find method to look for the first record that meets the criteria that was specified. In this case there was no criteria so it'll just set focus on the first record of the forms recordset.

the other part after else Last_Date is a variable that was declared, in which I don't actually see that it was declared anywhere so if option explicit wasn't set in then the program should set Last_Date as a variant variable.
you'll need to set Last_Date = &quot;something&quot;.value

I've created something similar to this in my db. If you would like to use the code I created then I can help you out and fix it to match your needs.

if you want to use this code then a couple of questions. I'm assuming that you have a table for &quot;work orders&quot; and a seperate table for &quot;calibration/repair&quot;? If so are these 2 tabels combined in the form?

Currently seeking for position around Boston, MA. kenphu@yahoo.com

 

[ve7epq]

Hi kphu. LAST_DATE is &quot;&quot; at the start of this exercise on the open form WORK ORDERS {TextBox}. All info is stored in a database called WORK ORDERS. I am searching my query of that database called qrySEARCH because the query places the items in question ordered alphabetically Ascending and by Date descendting. What I am trying to do is search the query to find the first record in the query where the TYPE and SERIAL_NUMBER match the type and serial number just typed in on the form. If it finds a date....that becomes LAST_DATE else LAST_DATE = DATE

 

[kphu]

Hi ve7epq,

I'm having a hard time understanding you because you reference a &quot;database&quot;; databases have many components inside of it and in regards to access it includes all tables, queries, forms etc.

I think this is what your saying.

Last Date is a text box on a form called WORKORDERS.
You have a query called qrySEARCH.
The qrySearch searches thru a &quot;table&quot;(?) called WORKORDERS(?) using the criteria of TYPE and SERIAL_NUMBER.

What you would like to do is set LAST_DATE equal to DATE if no record is found with TYPE and SERIAL NUMBER match?

AND LAST_DATE would be set to the DATE of the record that was found?

 

[ethorn10]

I've used the option I mentioned above a few times and it works fine. Some code:

Dim bool_type, bool_sn As Boolean

bool_type = False
bool_sn = False

rst.MoveFirst
Do While Not rst.EOF
     If (rst!TYPE = txt_type.Text) Then
          bool_type = True
          Exit Do
     End If
   rst.MoveNext
Loop

'you want both to match so if type wasn't found, then return Date() and exit subroutine
If (bool_type = False) Then
     LAST_DATE = Date()
     Exit Sub
End If

rst.MoveFirst
If (bool_type = True) Then
     Do While Not rst.EOF
          If (rst!SERIAL_NUMBER = txt_serialnumber.Text) Then
               bool_sn = True
               LAST_DATE = rst!LAST_DATE
               Exit Do
          End If
        rst.MoveNext
     Loop
End If

I *think* this will do the trick.

 

[ve7epq]

Thanks again both of you. KPHU you are correct in each and every assertion. I am sorry if my name conventions are very clear. It has been a while since I did any coding and I realizing that my use of labels and forms and tables with the same or similar name is very confusing.

Anyway, you did get the flow correct and the names of the form (WORK ORDERS) and the query (qrySEARCH) and the table (WORK ORDERS) correct.

 

[kphu]

Does ethorn 10's suggestion work for you? If not I have a solution. I'll post as soon as I'm done.

Ken

Currently seeking for position around Boston, MA. kenphu@yahoo.com

 

[ve7epq]

Not so far. I have to make some adjustments in names as I was working off of the names in the query...and they are different than in the table

Let me know thanks both of you. I really appreciate it

Mike

 

[kphu]

You can get rid of the qrySearch query as I'll create the code that will do the search.

you'll put this code on the On_exit property of the SERIAL_NUMBER. I assumed that the user would enter in a value for TYPE first and then tab to SERIAL NUMBER and enter a value in there after (if not then place this code in on exit property of TYPE). Once the user inputs a value for SERIAL NUMBER and tabs to the DATE text box the code will run and place a value into the DATE text box field.

you'll need to change certain words in there so that it'll relate to your database.


dim db as database, rec as recordset, strdate as string
dim intA, clpnum, recnt as integer

'checks the values in TYPE and SERIAL_NUMBER to make sure its not blank

if isblank(me.SERIAL_Number) then
me.TYPE.setfocus
me.SERIAL_NUMBER.setfocus
exit sub
else
if isblank(me.TYPE)then
me.type.setfocus
exit sub
else
' determines that the values are not blank
' opens the table called WORKORDERS' record set and searches for the records that contains the search criteria.
set db = currentdb()
set rec = db.openrecordset(&quot;WORKORDERS&quot
rec.movelast
recnt = rec.recordcount
rec.movefirst

for intA = 1 to recnt
if rec.eof = true then
else
if rec(&quot;TYPE&quot.value = Me.TYPE and rec(&quot;SERIAL_NUMBER&quot.value = me.SERIAL_NUMBER then
if strdate = &quot;&quot; then
date = rec(&quot;Date&quot.value
else
if strdate < rec(&quot;Date&quot.value then
strdate = rec(&quot;Date&quot.value
else
end if
end if
else
end if
end if
end if
end if
rec.movenext
next intA

me.DATE = strdate

 

[kphu]

oops forgot to include the part where if it doesn't find the records with the criteria then it should set the date = to date.

so after next intA delete me.DATE = strdate

and add in the following line.

if strdate = &quot;&quot; then
me.date = date ' sets the date as today's date.
else
me.date = strdate ' sets the date as the higest/last date in the record that matched the criteria.

 

[kphu]

errr... being really careless, I apology.

forgot to include end if.

so add &quot;end if &quot; after the last line.

the entire code should look like this.

dim db as database, rec as recordset, strdate as string
dim intA, clpnum, recnt as integer

'checks the values in TYPE and SERIAL_NUMBER to make sure its not blank

if isblank(me.SERIAL_Number) then
me.TYPE.setfocus
me.SERIAL_NUMBER.setfocus
exit sub
else
if isblank(me.TYPE)then
me.type.setfocus
exit sub
else
' determines that the values are not blank
' opens the table called WORKORDERS' record set and searches for the records that contains the search criteria.
set db = currentdb()
set rec = db.openrecordset(&quot;WORKORDERS&quot
rec.movelast
recnt = rec.recordcount
rec.movefirst

for intA = 1 to recnt
if rec.eof = true then
else
if rec(&quot;TYPE&quot.value = Me.TYPE and rec(&quot;SERIAL_NUMBER&quot.value = me.SERIAL_NUMBER then
if strdate = &quot;&quot; then
date = rec(&quot;Date&quot.value
else
if strdate < rec(&quot;Date&quot.value then
strdate = rec(&quot;Date&quot.value
else
end if
end if
else
end if
end if
end if
end if
rec.movenext
next intA

if strdate = &quot;&quot; then
me.date = date ' sets the date as today's date.
else
me.date = strdate ' sets the date as the higest/last date in the record that matched the criteria.
end if

 

[ve7epq]

Boy I really appreciate your help. I have been working on the code all afternoon.

Problem

dim db as Database
comes up as an error.....not defined?

also I discovered that in the main database I stored the value MAKEID (key number) that refers to the TYPE. SO I am trying to sort that out

Main issue is that
Dim db as database isnt working...

oh ya....Access 2000 doesnt use isblank()
I had to change that to isNull()

Mike

 

[kphu]

on your vbe editor go to tools then click references.

I have the following checked.

Visual Basic for applications
Microsfot Access 9.0 Object Library
OLE AUtomation
Microsoft DAO 3.6 Object Library

if any of those are missing then select it.

That should do the trick.

 

[ve7epq]

Two more issues....

Why
if isblank(me.SERIAL_Number) then
me.TYPE.setfocus
me.SERIAL_NUMBER.setfocus
exit sub
else
if isblank(me.TYPE)then
me.TYPE.setfocus
exit sub
else

TYPE in both instances of isNull()..is that a type
and now it is not accepting this line


set rec = db.openrecordset(&quot;WORKORDERS&quot


Mike

 

[kphu]

I made an assumption that &quot;Type&quot; is a control name on your form. If not then change that to the name of the control that holds the Type data.

I assumed &quot;WORKORDERS&quot; is the name of the table that holds all of the data. Change that to the correct table name.

If you have a query named WORKORDERS as well I would suggest to rename it as qryWORKORDERS just to differentiate.

Let me know if that works out. I'll be leaving in about 10 mins but will be back in the office tommorrow.

 

[ve7epq]

Excellent help...thank you so much. Only catch with the code is in this case:


DATE = Today (31 Dec 03)
The sort finds a previous record for Dec 9th for the same unit. It correctly makes LAST_DATE = 9 Dec 03 and then resets DATE (which should still read 31 Dec 03) to 9 Dec 03.

In the other case, where there is no other record it does correctly name LAST_DATE equal to today.

I really appreciate your help in this matter!

Mike

 

[ve7epq]

Let me Clarify what I found. When I find an older WORK ORDER and get a correct match, the routine changes the present setting in the field DATE, as well as writes the older date into LAST_DATE. The data in DATE I dont want to change. It may be today or it could be archival data....all I want the subroutine to affect is LAST_DATE

By the way, enjoy New Years Eve

Mike