pseudo code help

I

ifoundgoldbug

This is basically what I want to do but I am unsure how to go about it
the best way.

I have a bunch of tools in my DB and I would like to make something
like this

Sub Form open

check to see which parts need PM by checking the PM date VS todays
date

pop up some form of notification that PM is needed

Generate PM work order

End Sub Form open


Sub Enter PM

enter in a date for PM for a particular part

Search the DB by part Number and change all PM dates to the new PM
date

End Sub


Thank you so much for your help.

Gold Bug
 
D

Douglas J Steele

Without knowing what your tables look like, it's practically impossible to
give specific answers.

If all you need to do is check whether there are some parts that need PM,
you can use something like:

DCount("*", "MyTable", "PMDate <=" & Format(Date(), "\#mm\/dd\/yyyy\#")

To open a recordset that returns all of the parts that need PM, you can use
something like:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM MyTable WHERE " & _
"PMDate <=" & Format(Date(), "\#mm\/dd\/yyyy\#"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
Do Not While rsCurr.EOF
' Here, you can process each part one at a time

Loop

To change the PM Date, the best approach is to use an UPDATE query.

Dim strSQL As String

strSQL = "UPDATE MyTable SET PMDate = " & _
Format(NewPMDate, "\#mm\/dd\/yyyy\#") & _
" WHERE PartNumber = " & PartNumber

CurrentDb.Execute strSQL, dbFailOnError
 
I

ifoundgoldbug

Thanks for the help doug

this is where I am at now

Private Sub Form_Load()

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM WorkOrder WHERE " & "PMDate <=" &
Format(Date, "\#mm\/dd\/yyyy\#")

' I think i am reading this right. in this sequal statement it returns
everything prior to days ' date??? i would like it to return all the PM
dates for the week so bascially a BETWEEN ' today() AND today() +7 if
you get what i am trying to do

Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
' Do While Not rsCurr.EOF

<i need something here that will take the results from the SQL querie
and put it into a pop up form with the PM's for the week>

' Loop


End Sub
 
D

Douglas J. Steele

strSQL = "SELECT * FROM WorkOrder WHERE " & "PMDate Between " &
Format(Date, "\#mm\/dd\/yyyy\#") & " And " & Format(Date + 7,
"\#mm\/dd\/yyyy\#")

Since you've given no details about your application, I cannot help you with
the second part.
 
I

ifoundgoldbug

appologies for the lack of details i just don't have it totally worked
out in my head yet either so there are not many details to give.

here is what I want after your SQL statement I would like all of the
data the query returns to pop up it's own form saying something to the
effect of Here are all the parts that need PM this week.

I am building this PM program from the ground up and basically am
bolting it (bad practice i know) onto an existing database that is
heavily used by my department. So when they access our most heavily
used form i would just like it automatically search the database and
bring up that information without us having to search through it (we
have approx 10K records)

if you need more detail I will try to help as best I can
 
I

ifoundgoldbug

Appolgies i poked around for a while and i got it to work.

thank you for your time I appreciate it

Gold Bug
 
Top