auto number

[RainLover]

Okay RainLover I changed my field name to "PO". I took the single user code and tried it and it doesn't work for me. I'd also like to know how your single user form is updated. I see that you have it written for AfterUpdate but don't see where it actually gets updated.

I assumed you downloaded my sample for single user.

Follow this.

Open the Database by holding down the Shift key.

Open The form in design view.

Select the field txtMyLetter, On the Right you should see an elipse. Click on the ellipse. It should take you to the code.

Note the DB was designed to add a prefix. Like A1, A2. B1, B2, B3 etc. I know you don't need that but that is the way I wrote it.

Once the Focus/Mouse leave that Field it will be written to the table. It is very difficult to get that number back if you want to later. My DB was written for display purposes. A production DB would have the update (Allocation of the number) just before the form is written to the table. It is the most efficient without writing days of code. My Multi User does this but I advise against using it as there is very little support for it.

Please bear in mind, ( Like Others are telling you) once you use the number it is lost.

My question is why do you want to do this. I can see no real advantage.

There is one other alternative is to display in a Unbound Box of its own the highest number used so far. You could even add a one to it if you wanted.

But caution, most of the time this is the number that will be recorded but not guaranteed. If you can't guarantee your data you are not writing correctly.

Next question Please. PS Bed time for me in 1 hour.

BTW am I on the right track that you want.

 

[gizmogeek]

CJ_London how would your code:
[PO#] = nz(DMax("[PO#]", "Purchase Order Table")) + 1 work in a multiuser environment?
Thanks!!

 

[RainLover]

I finally had a look at your sample Database.

It is wrong. You connect PKs to FKs not PKs to PKs

You have better look at some database designs, Look in the AWF archives.

Also have a read of this.

 

[gizmogeek]

Cj_London are there repercussions if I want to compact and repair my database? I think I'll also put a Next PO button in.

I don't like the way the naming convention that they used in tables and fields but I'm a bit stuck with it now. Just and after thought.

 

[RainLover]

Try

Me.[PO#] = Nz(DMax("[PO#]", "Purchase Order Table")0) + 1

I think that is right. Not tested.

 

[jdraw]

I think there is a minor syntax issue with Rain's code

Me.[PO#] = Nz(DMax("[PO#]", "Purchase Order Table"), 0) + 1

 

[RainLover]

I think there is a minor syntax issue with Rain's code

Me.[PO#] = Nz(DMax("[PO#]", "Purchase Order Table"), 0) + 1

Nice Pick Up

 

[jdraw]

gizmogeek,

Have you reset your relationships as was mentioned by Rain in #26?

 

[RainLover]

I have given you a Sample of a Database. Did you look closely at the design.

I have given you a Document that helps explain some of the jargon used and a simple method you could adopt.

But you still have problems. You are looking for a quick fix.

Suggest you write a 50-100 page document of what you want. Post it here and you will have bunches of people willing to do you work for you. (I jest)

But you have to read documents. Study other Database. Google for Access Tutorials. You can't go on not knowing what a Primary Key (PK) is.

As far as been given this database that someone has told you to fix then I suggest you fix it. I will help you if you help yourself but this stuff about someone else gave it to me means you have to fix it. You can't continue building on top of a poor foundation.

Sorry, but it is late here and I do not mean to offend. Maybe I am a little cranky.

 

[jdraw]

Gizmo geek,

I concur with Rain. You can't fix a database without understanding some basic concepts such as Normalization, Primary key and foreign key. AS he said, you can't continue on a bad foundation.

Here are a couple of links to help with concepts and rationale.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip
http://www.rogersaccesslibrary.com/forum/uploads/5/12-Steps_to_Better_Databases.zip

People here will help you with whatever you need, but they won't do it for you.

Good luck.

 

[gizmogeek]

No I didn't reset. I'm reading the links he sent and trying to understand the links he sent in 26. I didn't see them the first time as I think I was in a mode of just trying to fix. I don't understand why they joined the relationships the way they did.

 

[jdraw]

I don't understand what they did either.


Tables TblA and TblB are related, if the PK (primary key) in TblA is a FK(Foreign key) in TblB. Every table should have a PK. A PK Primary key is a value which uniquely identifies a record in a table from all other records in that table. That's what relational is all about.

You don't have PKs joined to PKs. See the attached jpgs

The problem jpg is from your tables and relationships.
The other Normal is from data models at http://www.databaseanswers.org


Research Pk and FK
http://databases.about.com/cs/administration/g/primarykey.htm
http://en.wikipedia.org/wiki/Foreign_key

 

[RainLover]

When I wrote my first DB I had no idea. Also no internet. I was working out bush with no help.

So I purchased a book to help me. It was either the Dummies book or the Idiots book.

I suggest you get a copy for your version. About $10.00 or maybe less.

I still have that book and refer to it on occasion.

Suggest you trash what you have and start again properly. There are also tutorials on the web of which some are excellent.

But don't blame someone else for your problem. It is no excuse. It is your problem now so fix it.

Also I would suggest small questions one at a time.

The first and most import thing to learn is normalisation.

Hope the attachments help.

 

[RainLover]

Wow. Dude I actually have that book. Thanks for the insult and I'm so sorry I asked for help. I think it was really uncalled for. What is this forum for if you can't ask for help?

Always happy to help point people in the right direction.

Anymore questions please post back.

Best of luck for your project.

 

[Solo712]

There is very little guarantee that the DMax will work as you wish. It will work 99% of the time but in a multiuser version the chances of the number duplicating increases.

In my signature their is a sample of how to use Dmax + 1 for single users and another for multiusers. The later is for advanced users only.

Just in case I am not the only one who thinks Rain's mostly talking through his hat, here is a simple way to make the possibility of duplicating a program-issued key pretty damn unlikely. The technique does not lock the table but locks out the other users from the key-generating code. The code should be the last section of the form's BeforeUpdate. The I/O file used to lock users out has to exist on a common network share.

'
On Error GoTo Err_handle
' 
'
sPath = U:\AccessUtil\LockerUp.txt"
 
If TBusy(sPath) Then
     Err.Raise vbObjectError + 1000
End If
'
'  If the code proceeds to here then the 'LockerUp' file   
'  was open by this user and the key-generating section  
'  is now exclusively owned by him/her until the file 
'  is closed.  
'  
'  Issue the key 
'
Exit Sub
' 
'
Err_handle:
  If Err.Number = vbObjectError + 1000 Then
    If MsgBox("The table you are updating is locked ! " & vbCrLf & _
               "Press Ok to retry or Cancel to Quit !", vbOKCancel + vbInformation) = vbOK Then
       Resume
    Else 
       Resume Update_Cancel
    End If
  End If
'
'  handle other errors 
'
Update_Cancel:
   Cancel = True
End Sub
 
' --------------------------------------------------------------
' In the Form's AfterUpdate Event
'
If ffOpen Then 
Close ff
ffOpen = False
End if
'
'--------------------------------------------------------------
' global scope
Public ff as Long, ffOpen as Boolean
'
Public Function TBusy(strPath As String) As Boolean
TBusy = False 
On Error GoTo FileLocked
ff = FreeFile
Open strPath For Random Access Read Write Lock Read Write As ff
ffOpen = True
Exit Function
'
FileLocked:
TBusy = True
ffOpen = False
End Function

Best,
Jiri

 

[RainLover]

Solo

Do you have to be so Rude every time you face the keyboard.

 

[Galaxiom]

The technique does not lock the table but locks out the other users from the key-generating code. The code should be the last section of the form's BeforeUpdate. The I/O file used to lock users out has to exist on a common network share.

Why on earth resort to something so incredibly clumsy when the job can be done much more effectively inside the application?


In a large multiuser system there is a considerable chance of a glitch causing the the file to remain locked with all users waiting for the file to be released via timeout.

 

[Solo712]

Why on earth resort to something so incredibly clumsy when the job can be done much more effectively inside the application?

I was not aware I was outside the app ?

In a large multiuser system there is a considerable chance of a glitch causing the the file to remain locked with all users waiting for the file to be released via timeout.

Yeah, right ! But you see, one, it would only not be 'all users' but only the hundred or so rolleyes inserting a key in that table at the same time (an all-important point which you don't get !). Two, do yourself a favour and read a bit on winapi file lock/unlock functions. You will be free of fears that a file locked by Windows poses a real problem. It's certainly not anything approaching a situation of a db transaction getting stuck. You are not raising any real issues with what I am proposing.

Best,
Jiri

 

[Solo712]

Solo

Do you have to be so Rude every time you face the keyboard.

It's not every time, Rain ; it's only when I see posts like yours in #39 !

Best,
Jiri

 

[Galaxiom]

I was not aware I was outside the app ?

I would have thought it was obvious that the flag file was outside the app.

sPath = U:\AccessUtil\LockerUp.txt"

The same thing can be done inside the app by locking a table. This can be combined with the allocation of the key from the same table without the need to use DMax.

Store the next number as a single record in a field. Open a recordset with dbDenyRead + dbDenyWrite while the number is read and incremented.

There is no need for the user to be aware of the process. Instead of the clunky offer to the user to cancel the update or try again, simply catch the error and try again until you get one allocated.