Updating records with a fixed sequence...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carlo Mechanic - 28 Feb 2005 18:40 GMT

Hi everyone,

I am very new in Access 2000 with little knowloedge about coding. Here's my
problem:
1. I have 2 tables - MachineMaster and MachineJobs.
2. MachineMaster determines whether a machine is available for job orders
posted in the MachineJobs table. Availability is determined by the Shift
Foreman.
4. Machines are coded as A1, A2, A3, A4, B1, B2, B3, B4 in fixed sequence.
3. MachineJobs is a list of Job Orders waiting to be assigned and processed.

The task is to create a SINGLE COMMAND BUTTON that will do the following
steps:
a. Determine the available machines in MachineMaster
b. Sort the the MachineJobs by date and check if Complete Date is null
(meaning job is still open)
c. Assign a Machine code in the fixed sequence A1, A2, A3..etc. to a
MachineJob.
e. Assign a Job number in sequence.. ex: Job-0001, Job-0002, Job-0003...

My question is: HOW DO I WRITE THIS IN VB CODE for the command button?

Please help and kindly be specific as I am only 2-days old with Access 2000.

Any help would be greatly appreciated.

Reply to this Message

John Spencer (MVP) - 28 Feb 2005 23:07 GMT

Several questions-
1) What do you do if there are more jobs than available machines?
2) Are all the machines listed in your example or are there more machines? If
there are always 4 machines for each letter, then which letters are involved?
3) What does the shift foreman do to mark a Machine as available in the
MachineMaster table? Does he set a value somewhere?

4) What version of Access?

5) What are the relevant field names and field types in your tables? Guessing
that you have something like:

MachineMaster
-MachineCode / text field
-Available / Yes-No

MachineJobs
-JobNumber / ???
-MachineCode / Text field
-CompleteDate / DateTime field
-SomeOtherField that is populated to create the MachineJob

> Hi everyone,
>
[quoted text clipped - 21 lines]
>
> Any help would be greatly appreciated.

Reply to this Message

Carlo Mechanic - 01 Mar 2005 14:47 GMT

Hi John,

Thank you for your response. Here are my answers to your questions:

1. There will always be more jobs than availble machines. In fact, at an
average, there will always be 220 jobs for 6 available machines. Therefore
the task is to sort all available jobs by Date (ascending) order, and
distribute the machines evenly on a fixed sequence - A1, A2, A3, A4, B1, B2
(b3 and b4 are backups in case there are more than 220 jobs)

2. There are more Machines than the example. But these machines have
pre-defined in the MachineMaster. As of now, A, B, C and D are the involved
letters, where B and D have 2 backups (B3, B4, D3 and D4).

3. The shift foreman looks at his COMPLETED CHART for the previous shifts
and pretty much decides the availability of each machine. He uses a YES/NO
box to trigger the availability of the machine. No set value.

4. I am using this version - MS Access 2000 (9.0.6926 SP-3)

5. You're right on the mark for the Table field names....

Please let me know if you have other questions. Thanks in advance for the
help.

> Several questions-
> 1) What do you do if there are more jobs than available machines?
[quoted text clipped - 43 lines]
> >
> > Any help would be greatly appreciated.

Reply to this Message

John Spencer (MVP) - 04 Mar 2005 02:27 GMT

Sorry, I've been out of the loop for this forum for the last couple of days. I
won't be able to answer this right now, but I should be able to get to it this
weekend if you haven't developed a solution yet.

One more question, how do you determine the order of Machine Jobs to be assigned
to a machine? By some other field such as Priority, OrderDate, Invoice Number
or some combination of these? Or do you just do it randomly? This might get you started.

To identify available machines:
SELECT MachineCode
FROM Machines
Where Available = True
Order By MachineCode

To identify available jobs:
SELECT JobNumber, MachineCode, CompleteDate, SomeSequenceField
FROM MachineJobs
WHERE CompleteDate Is Null and JobNumber Is Null
Order By SomeSequenceField

> Hi John,
>
[quoted text clipped - 68 lines]
> > >
> > > Any help would be greatly appreciated.

Reply to this Message

Carlo Mechanic - 04 Mar 2005 17:23 GMT

Hi John,

Thanks for the tip. Haven't written a solution yet. Please help.

The available jobs are sorted a a Priority Sequence, Priority Date (oldest
date first or ascending), and PartNumber.

Question: How do I structure the SQL Statement into my CommandButton_Click()
event?

Private Sub cmdAssignMachines_Click()
Dim MachineCode as ???
Dim strSql01 as ???
Dim strSql02 as ???

'Check available machines

SELECT MachineCode FROM Machines Where Available = True
Order By MachineCode

' Result is - A1, A2, A3, A4, B1 and B2 are available - how will this
data be stored? how will the vba code know which is the last machine to
trigger a loop in the process.

' Identify available jobs

SELECT JobNumber, MachineCode, CompleteDate, SomeSequenceField
FROM MachineJobs
WHERE CompleteDate Is Null and JobNumber Is Null
Order By SomeSequenceField

' Result is: 210 jobs available waiting to be assigned a machine

' Then what happens??? What code will be next if I want to do this:
' If first record, assign A1
' move next, assign A2
' move next, assign A3
' move next, assign A4
' move next, assign B1
' move next, assign B2
'If machine is the last in the machine order, loop back to A1
and repeat process
' If first record, assign A1
' move next, assign A2
' move next, assign A3
' move next, assign A4
' move next, assign B1
' move next, assign B2

'Is it End of File? Yes, end process. No, repeat until end.

Thanks in advance for the assistance.

> Sorry, I've been out of the loop for this forum for the last couple of days. I
> won't be able to answer this right now, but I should be able to get to it this
[quoted text clipped - 88 lines]
> > > >
> > > > Any help would be greatly appreciated.

Reply to this Message

John Spencer (MVP) - 06 Mar 2005 20:55 GMT

UNTESTED SUB Follows. Put this in a module and call it from a button when you
need it. Hope this helps. I was trying to come up with some simple SQL to do
this, but no inspiration struck.

Sub cmdAssignMachines()
Dim dbAny As DAO.Database
Dim rstTarget As DAO.Recordset
Dim rstSource As DAO.Recordset
Dim StrSQL As String

Set dbAny = CurrentDb()
'Get available machines
StrSQL = "SELECT MachineCode FROM Machines " & _
"Where Available = True " & _
"Order By MachineCode"

Set rstSource = dbAny.OpenRecordset(StrSQL)

If rstTarget.RecordCount < 1 Then
'No machines are marked available
MsgBox "No Machines available"

Else
'Get jobs that are not assigned. I am not sure
'of the criteria in this query. You may need to modify
'the where criteria depending on your business rules.
StrSQL = "SELECT JobNumber, MachineCode, " & _
"FROM MachineJobs " & _
"WHERE CompleteDate Is Null " & _
"AND JobNumber Is Null " & _
"AND MachineCode is Null"
"Order By [Priority Sequence], " & _
"[Priority Date] Desc, [PartNumber]"

Set rstTarget = dbAny.OpenRecordset(StrSQL)
If rstTarget.RecordCount < 1 Then
MsgBox "No Jobs to assign"
Else
'Do the work by stepping through all the MachineJobs
While Not rstTarget.EOF

rstTarget.Edit
rstTarget!MachineCode = rstSource!MachineCode
rstTarget.Update
rstTarget.MoveNext

rstSource.MoveNext

'Whoops! reached the end of the Available Machines
'Go back to the first available one.
If rstSource.EOF Then rstSource.MoveFirst

Wend

End If
End If

End Sub

Reply to this Message

Carlo Mechanic - 07 Mar 2005 15:35 GMT

Hi John,

Thanks again for the tip. I will definitely try your suggestion today.

Just a quick question and hope you can reply immediately. As per your
instruction, I will:

1. Create a NEW MODULE in the modules TAB of Access 2000.
2. Paste this suggested code, modify it based on our criteria and then save.
3. Insert a Command Button to call the function.

This is my question:
How do I call the function into the Command Click() event?

Thanks for your help.

> UNTESTED SUB Follows. Put this in a module and call it from a button when you
> need it. Hope this helps. I was trying to come up with some simple SQL to do
[quoted text clipped - 54 lines]
>
> End Sub

Reply to this Message

Carlo Mechanic - 07 Mar 2005 16:21 GMT

Hi John,

I got the suggested and inserted it in the event tab of my Command0 cmd
button. I am getting a "User-defined" type error at DIM dbAny as DAO.Database

Here is the full code:

Option Compare Database

Private Sub Command0_Click()
Dim dbAny As DAO.Database ' compile error in this line - User-defined type
not defined.'
Dim rstTarget As DAO.Recordset
Dim rstSource As DAO.Recordset
Dim StrSQL As String

Set dbAny = CurrentDb()
'Get available machines
StrSQL = "SELECT MachineCode FROM MachineCodes " & _
"Where CategoryA = True " & _
"Where CategoryB = True " & _
"Where Available = True " & _
"Order By MachineCode"

Set rstSource = dbAny.OpenRecordset(StrSQL)

If rstTarget.RecordCount < 1 Then
'No machines are marked available
Msgbox "No Machines available"

Else
'Get jobs that are not assigned. I am not sure
'of the criteria in this query. You may need to modify
'the where criteria depending on your business rules.
StrSQL = "SELECT ProductionOrder, MCode, " & _
"FROM JobMaster_test2 " & _
"WHERE DateCompleted Is Null " & _
"AND JobNumber Is Null " & _
"AND MCode is Null " & _
"Order By Usr " & _
"LatesDate, PartNumber "

Set rstTarget = dbAny.OpenRecordset(StrSQL)
If rstTarget.RecordCount < 1 Then
Msgbox "No Jobs to assign"
Else
'Do the work by stepping through all the MachineJobs
While Not rstTarget.EOF

rstTarget.Edit
rstTarget!MachineCode = rstSource!MachineCode
rstTarget.Update
rstTarget.MoveNext

rstSource.MoveNext

'Whoops! reached the end of the Available Machines
'Go back to the first available one.
If rstSource.EOF Then rstSource.MoveFirst

Wend

End If
End If

End Sub

Reply to this Message

John Spencer (MVP) - 07 Mar 2005 21:59 GMT

In that case, you have probably not registered the dao library.

A2K and later versions default to ADO which does not have a Database Object.
Database is an Object of DAO which A97 defaults to. To use your code, you should:

Quoting Doug Steele
Database is a DAO object. By default, Access 2000 uses ADO.

With any code module open, select Tools | References from the menu bar,
scroll through the list of available references until you find the one for
Microsoft DAO 3.6 Object Library, and select it. If you're not going to be
using ADO, uncheck the reference to Microsoft ActiveX Data Objects 2.1
Library

If you have both references, you'll find that you'll need to "disambiguate"
certain declarations, because objects with the same names exist in the 2
models. For example, to ensure that you get a DAO recordset, you'll need to
use Dim rsCurr as DAO.Recordset (to guarantee an ADO recordset, you'd use
Dim rsCurr As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

(Hopefully this explains to you why you can't just use "DIM rst as
Recordset")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

> Hi John,
>
[quoted text clipped - 121 lines]
> >
> > End Sub

Reply to this Message

Carlo Mechanic - 09 Mar 2005 15:05 GMT

Hi John,

It worked! Thanks a million.

I activated all the necessary libraries as you instructed and got the code
working. It now updates all open jobs with a corresponding Machine Code.

I do have other questions related to this application.

Is it alright to consult you again?

Hope you're doing well.

> In that case, you have probably not registered the dao library.
>
[quoted text clipped - 152 lines]
> > >
> > > End Sub

Reply to this Message

John Spencer (MVP) - 10 Mar 2005 01:17 GMT

It's alright to post your questions in this or other forums. If you want to
consult me specifically or other persons in the group and do it for a lot of
questions, then you might consider hiring a consultant.

For individual questions that can be answered briefly, this forum is great. For
consulting on a large number of problems or complex problems you may need to pay
someone. We are volunteers on these forums.

> Hi John,
>
[quoted text clipped - 165 lines]
> > > >
> > > > End Sub

Reply to this Message