Assign unique numbers for each...

  • Thread starter Thread starter K. Mortensen
  • Start date Start date
K

K. Mortensen

All-
I have been stumbling through Access (I have 2000 & 2007 so I can work with
either one) trying to figure out how to accomplish the following with no luck
at all:

I need a table that will keep track of job numbers (unique per job)

And then multiple tables that will keep track of unique task numbers (unique
per job, per task)

For example, I could end up with the following data:

JOBS: TASKSA: TASKSB:
1 A1 B1
1 A2 B2
2 A1 B1
3 A1 B1

I am envisioning a form where the user would tell the db what job they need
a new task number for and what type of task number they need, and based on
whatever sequential number is next the db just gives it to the user and adds
the record into the appropriate table(s). But like I said, I havnt even been
remotely successful...and am not sure where to start. If someone could give
me a friendly nudge, I would really appreciate it!
 
Here is an example for Jobs
You create a table for CustomJobNumbers.
Only one field, make it a number - long integer.
This table only has one row in it. It stores the number for the last used
JobNumber.
You can get the next available job number with coeld like this in the Before
Update event for the form.

Dim varResult As Variant

varResult = DMax("[CustomJobNo]", "TableCustomJobNo")
If IsNull(varResult) Then
varResult = 1
Else
Me.CustomJobNo= varResult
End If


In the After Update event for the form, you update the table for
CustomJobNumbers with the number just used like this.

Dim db as DAO.Database
Dim strSQL as String

Set db = dbengine(0)(0)

strSQL = "UPDATE CustomJobNumbers " _
& "SET CustomJobNo = " & Me.CustomJobNo & ""
Debug.Print strSQL
db.Execute strSQL, dbFailOnError
Set db = Nothing

You could do a similar thing for tasksA and tasksB

Jeanette Cunningham
 
Here is an example for Jobs
You create a table for CustomJobNumbers.
Only one field, make it a number - long integer.
This table only has one row in it. It stores the number for the last used
JobNumber.
You can get the next available job number with coeld like this in the Before
Update event for the form.

Dim varResult As Variant

varResult = DMax("[CustomJobNo]", "TableCustomJobNo")
If IsNull(varResult) Then
varResult = 1
Else
Me.CustomJobNo= varResult
End If
Click to expand...

Because the Microsoft Jet database engine has a read cache and lazy
writes, you can get duplicate values in your custom counter field if
two applications add records in less time than it takes for the cache
to refresh and the lazy-write mechanism to flush to disk.

Those words are not mine but those of the Access/Jet team. See:

How To Implement Multiuser Custom Counters in Jet 4.0
http://support.microsoft.com/kb/240317/en-us

Jamie.

--
 
Thanks Jamie,

Jeanette Cunningham

Jamie Collins said:
Here is an example for Jobs
You create a table for CustomJobNumbers.
Only one field, make it a number - long integer.
This table only has one row in it. It stores the number for the last used
JobNumber.
You can get the next available job number with coeld like this in the
Before
Update event for the form.

Dim varResult As Variant

varResult = DMax("[CustomJobNo]", "TableCustomJobNo")
If IsNull(varResult) Then
varResult = 1
Else
Me.CustomJobNo= varResult
End If
Click to expand...

Because the Microsoft Jet database engine has a read cache and lazy
writes, you can get duplicate values in your custom counter field if
two applications add records in less time than it takes for the cache
to refresh and the lazy-write mechanism to flush to disk.

Those words are not mine but those of the Access/Jet team. See:

How To Implement Multiuser Custom Counters in Jet 4.0
http://support.microsoft.com/kb/240317/en-us

Jamie.
Click to expand...
 
Sorry for the ignorance, but do you mind giving me a brief break-down of the
code you have here?

Jeanette Cunningham said:
Thanks Jamie,

Jeanette Cunningham

Jamie Collins said:
Here is an example for Jobs
You create a table for CustomJobNumbers.
Only one field, make it a number - long integer.
This table only has one row in it. It stores the number for the last used
JobNumber.
You can get the next available job number with coeld like this in the
Before
Update event for the form.

Dim varResult As Variant

varResult = DMax("[CustomJobNo]", "TableCustomJobNo")
If IsNull(varResult) Then
varResult = 1
Else
Me.CustomJobNo= varResult
End If
Click to expand...

Because the Microsoft Jet database engine has a read cache and lazy
writes, you can get duplicate values in your custom counter field if
two applications add records in less time than it takes for the cache
to refresh and the lazy-write mechanism to flush to disk.

Those words are not mine but those of the Access/Jet team. See:

How To Implement Multiuser Custom Counters in Jet 4.0
http://support.microsoft.com/kb/240317/en-us

Jamie.
Click to expand...
Click to expand...
 
Essentially it is saying that if you have users both trying to add a new job
number at the same time, there could be a problem with both getting the same
number because Jet uses a slight delay with writing to disk.
If you are using Jet 4.0, use can use the code provided if you have set a
reference to the correct library for ADO ( as explained in the steps)
The method uses a transaction and some code to handle locking issues to make
sure a number is only used once.
The transaction effectively keeps a second user from accessing the counter
table before the first user has has finished saving their changes and has
updated the
counter table.

step1 creates the counter table
steps 2 and 3 set up the table with values for the first record
Test program
step 1 is creating a new form and a new module
step 2 is creating the appropriate references for the database to use ADO
( if you don't already have that reference set)
step 3 is code to put in a standard module
step 4 is setting up the form with a button and text box
step 5 is code to put behind the button on the new form to test the code and
see how it works
If you read the code in step 5 you can see how it is updating the counter
table
It calls the code for NextKeyValue that you previously (step 3) put into a
standard module.
This test runs through this process 100 times in succession - for a
convincing test.

There is a link to a reference on how to set up a multi-user custom counter
for DAO

Jeanette Cunningham

K. Mortensen said:
Sorry for the ignorance, but do you mind giving me a brief break-down of
the
code you have here?

Jeanette Cunningham said:
Thanks Jamie,

Jeanette Cunningham

Jamie Collins said:
On Jan 29, 11:46 pm, "Jeanette Cunningham"
Here is an example for Jobs
You create a table for CustomJobNumbers.
Only one field, make it a number - long integer.
This table only has one row in it. It stores the number for the last
used
JobNumber.
You can get the next available job number with coeld like this in the
Before
Update event for the form.

Dim varResult As Variant

varResult = DMax("[CustomJobNo]", "TableCustomJobNo")
If IsNull(varResult) Then
varResult = 1
Else
Me.CustomJobNo= varResult
End If

Because the Microsoft Jet database engine has a read cache and lazy
writes, you can get duplicate values in your custom counter field if
two applications add records in less time than it takes for the cache
to refresh and the lazy-write mechanism to flush to disk.

Those words are not mine but those of the Access/Jet team. See:

How To Implement Multiuser Custom Counters in Jet 4.0
http://support.microsoft.com/kb/240317/en-us

Jamie.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top