Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Auto fill (2002)

    I've created a simple database with an Autonumber field, which is an Order No, and an end user name. Say I want to allocate 100 orders to end user 'Fred', can I autofill the Autonumber without keep pressing enter and having to fill in the next field? Or is there another way.

    Rob

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Auto fill (2002)

    If you want to allocate numbers to different people, then you can't use an autonumber. Access fills the autonumber field by itself as soon as you enter anything into the form. BTW, you can set enabled=false and locked=true for that autonumber control so the cursor doesn't even stop there.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    Rob

    Using Autonumbers for your order numbers isn't really advisable. First off you have no control of the end result.
    You may even come across problems when using compact & repair.
    You also have to create a record before the Autonumber can be generated.

    The best advice would be to change your tables order number field to "Number" long integer.
    With some coding you can then increment from the last record created:

    DoCmd.GoToRecord , , acNewRec
    Forms!frmMyForm!OrderNo = DMax("OrderNo", "TblOrders") + 1

    If you wanted to allocate the next 100 Order No's to Fred, you could code this also to create the next 100 records, a "Do Until"
    "Loop" coding would do this without filling out the orders first.
    Some one on the forum would help you with this I'm sure.

  4. #4
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    Thanks, both of you. That's helped me. Anyone know how I do a "Do Until" loop?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Auto fill (2002)

    What specifically are you trying to do with the loop? A Do Loop can have the condition tested in the Do statement or in the Loop statement. If you test the condition at the top, the loop will be executed zero to many times. If you test at the bottom, the loop will be executed 1 to many times.

    If you write

    Do Until rst.EOF
    ...
    Loop


    you are putting the test first so that if the recordset is already at the end, the Do Loop will never be entered (zero to many iterations).

    If you put the test at the end like this:

    Do
    ...
    Loop Until rst.EOF


    the loop will always execute at least once, even if the recordset is already at the end of file marker (1 to many iterations).

    If you have specific questions, post back.
    Charlotte

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    I created a Purchase Order log and want to print blank orders with only the Order Number on, for people to fill in, then I will the database in as they are returned. I may want to give Dave 50 blanks, and Fred 50 blanks. So I want to print blank orders with order numbers 1 to 100.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Auto fill (2002)

    Something like this? I assumed you still have the AutoNumber field, so we don't need to assign the number ourselves. You must substitute the appropriate table name and field name.

    Sub CreateBlanks(UserID, HowMany)
    Dim cnn As ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim i As Long

    On Error GoTo ErrHandler

    Set cnn = CurrentProject.Connection
    rst.Open "tblPurchaseOrder", cnn, adOpenKeyset, , adCmdTableDirect

    For i = 1 To HowMany
    rst.AddNew
    rst!UserID = UserID
    rst.Update
    Next i

    ExitHandler:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Call it like this if you have a numeric UserID:

    CreateBlanks 12345, 50

    to create 50 records for the user with UserID 12345. Or if you want to use the user name:

    CreateBlanks "Fred Flintstone", 50
    CreateBlanks "Barney Rubble", 75

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    Here's an example for you to study.
    I converted it from a progress meter, but it suit's the purpose.
    Both fields must be filled in on the form, (You can set a default if you like.)
    Also, if you are starting from afresh, there must be an entry in both fields for name and number.
    I suggest starting the order table number from 1.

    Maybe you can change the code to auto populate this from the start.
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    This is just what I'm after Dave, but I'm getting a 'Compile Error : Variable not defined' when I run it in my database, and dbOpenDynaset is highlighted. I've changed table names etc, by the way.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    <P ID="edit" class=small>(Edited by D Willett on 06-Nov-03 17:39. Added foot note)</P>Rob
    You need to set your references to DAO3.6.
    Go to your form in design view and click the multi coloured icon in the toolbar at the top.
    From there, select Tools|References scroll down to Microsoft and tick the box with DAO 3.6.

    Then Debug from the menu and compile.

    Here's another variation of the demo, only this time using your AutoNumber field but, remember.
    If you delete your records, when you run the form again, it will create numbers from where it left off.
    It will only start from 1 again if you compact and repair.
    This is where you can have problems of duplicating orders !!

    BTW
    With this version, you don't have to insert records to start with.
    Attached Files Attached Files

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    I'm going to have to get some training. I'm getting a type mismatch error now, on this line :-Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblOrderLog", dbOpenDynaset)

  12. #12
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    Rob
    If you've checked the box for DAO3.6 then the code should run ok.
    Which version of My demo's are you using ?

    Demo1: OrderNo Field must be set to "Number",Long Integer in the properties.

    Demo2: OrderNo Field has been set back to "AutoNumber" the code is different behind the form for both demo's.

    If you want, create a new database and import your form along with your table.
    Zip the database and check it's under 100k and attach to a reply post.
    Both versions work ok here.

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    I've sort of got it working now, but no order numbers appear in the table. I can't attach it, it's too big.

  14. #14
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    Rob

    I covered this earlier, perhaps I didn't explain it clearly enough.

    From Previous Post : Also, if you are starting from afresh, there must be an entry in both fields for name and number.
    I suggest starting the order table number from 1.


    There must be 1 order No in the table for the form to work correctly.
    This is why I posted the 2nd demo which reverted back to your Autonumber format.

    In your orders table ( Copy your database and use the copy) delete all the records and make one entry, ie:

    NameField:Fred
    OrderField:1

    Then run the form again.
    Perhaps you are better going back to the Autonumber format.
    Sorry to have confused things.

  15. #15
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Auto fill (2002)

    Dave,
    It seemed to be when I deleted records after trying it out.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •