Page 1 of 3 123 LastLast
Results 1 to 15 of 40
  1. #1
    Join Date
    Dec 2013
    Posts
    22

    Unanswered: Get access to fill in multiple filelds in the table automatically based on Qty

    Hi,

    I'm developing a DB right now and I'm really stuck on this issue that I have.

    In my DB the user fills in the data such as Job no:,Scheduled delivery date,Qty,Serial no.

    The thing is each job no: would come in different quantities.
    for eg:Job no: RN-8928 may have Qty as 3
    I want the user to enter just the order no. and access should fill the table data as
    RN-8928-1/1
    RN-8928-1/2
    RN-8928-1/3

    with each entry getting serial number in sequence.


    Really could use some help

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by dg3447 View Post
    The thing is each job no: would come in different quantities.
    for eg:Job no: RN-8928 may have Qty as 3
    I want the user to enter just the order no. and access should fill the table data
    How will Access know that, for a given Job no (or is it order no?) the quantity will be 1, 2, 3 or whatever, if the user does not provide it?
    Have a nice day!

  3. #3
    Join Date
    Dec 2013
    Posts
    22

    Get access to fill in multiple filelds in the table automatically based on Qty

    Hi,

    Thank you for replying.
    The user will enter the quantity of the order no: and based on the quantity access should automatically fill in multiple fields.

    ex: J-8929 qty-2

    Access should fill in data as J-8929 1/2 and J-8929 2/2 with serial numbers 35321 & 35322 (ie: in sequence).

    Please reply soon as Im in dire need here.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Where does the serial number come from? If you want quick answers you should try to provide detailed information related to the problem. Please post, at least , the definition of the table involved in the process (table name, fields name and type).
    Have a nice day!

  5. #5
    Join Date
    Dec 2013
    Posts
    22

    Details

    Attached is the form that is being submitted,
    Serial number could be an auto number or a manual entry. Right now it is a manual entry.

    Here are the details

    Table name-Tasks
    Field names- ID,Job_no,Sl_no.,Qty,Branch,Status,Received_date,W eek Scheduled,Sales_ID,Delay_reason,New_Delivery

    The above mentioned is the main table and I have queries from this table based on different statuses. I have a form where the user submits the above mentioned details of an order/Job. Name of the form is "Task Details"

    Now this is the problem am facing
    Each job would come in different Qty.What is happening now is for ex: if Job no RN-8926 has the qty as 3 the user has to submit multiple forms to add data to the table i.e:
    RN-8926 1/3 , Sl_no: 35321
    RN-8926 2/3 , Sl_no: 35322
    RN-8926 3/3 , Sl_no: 35323.

    All are submitted through the form "TASK DETAILS"separately.

    What I am trying to achieve here is to jump through the pain of filling in the form multiple times and have access fill in the data automatically based on the quantity and also assign Sl_no if possible.i.e if the quantity is 3 user will have to enter only the first qty and access should fill in the rest of the two and assign sequential serial #

    Hope all this made sense.
    Please reply if further details are needed.

    Really appreciate your help.

    Regards,
    Daryl
    Attached Thumbnails Attached Thumbnails submitting form.JPG  

  6. #6
    Join Date
    Dec 2013
    Posts
    22

    DESIGN VIEW SNAP SHOT attached

    Attached is the snapshot of the design view of the table "Tasks"
    Attached Thumbnails Attached Thumbnails table snapshot.JPG  

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You did not provide the data type of each column of the table Tasks, so I'll suppose this:
    ID: AutoNumber
    Job_no: Text
    Sl_no: Number (Long)
    Qty: Number (Long)
    If any other column does not accept Nulls, I suppose that a default value is defined.

    On a form, you have 2 unbound (i.e. not linked to a table or query --> ControlSource property is empty) textbox controls: Text_JobNumber and Text_Quantity, and a command button control: Command_AddJob.

    Link the Click event of the command button to this procedure:
    Code:
    Private Sub Command_AddJob_Click()
        
        Const c_SQL As String = "INSERT INTO Tasks ( Job_no, Sl_no, Qty ) VALUES ( '@1', @2, @3 );"
        
        Dim lngSerialNumber As Long
        Dim strSQL As String
        Dim i As Long
        
        If Len(Nz(Me.Text_JobNumber, "")) > 0 Then
            If Nz(Me.Text_Quantity.Value, 0) > 0 Then
                For i = 1 To Me.Text_Quantity.Value
                    lngSerialNumber = Nz(DMax("Sl_no", "Tasks"), 0) + 1
                    strSQL = Replace(Replace(Replace(c_SQL, "@1", Me.Text_JobNumber), "@2", lngSerialNumber), "@3", 1)
                    CurrentDb.Execute strSQL, dbFailOnError
                Next i
            Else
                MsgBox "You must enter a quantity.", vbInformation, "Quantity missing"
            End If
        Else
            MsgBox "You must enter a job number.", vbInformation, "Job number missing"
        End If
        
    End Sub
    If, apart for these 2 textbox controls, the form is bound to the table Task or to a quary based on this table, you'll have to requery the form at the end of the Command_AddJob_Click procedure:
    Code:
    Me.Requery
    If the value in the column Qty must be the total value, use:
    Code:
    strSQL = Replace(Replace(Replace(c_SQL, "@1", Me.Text_JobNumber), "@2", lngSerialNumber), "@3", Me.Text_Quantity)
    Have a nice day!

  8. #8
    Join Date
    Dec 2013
    Posts
    22

    Thank you

    Thank you so much..will try and let you know

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  10. #10
    Join Date
    Dec 2013
    Posts
    22

    Sorry not working

    Hi,

    Tried what you told me to..But when i tried to debug it shows Compile error:Method or data member not found and highlights Text_JobNumber.
    I had created the two unbound text boxes exactly as you had written in the code.

    Really appreciate the help

  11. #11
    Join Date
    Jan 2014
    Posts
    29

    update query or dlookup function code

    If i understand you correctly, it is the second part of the number after the slash that you want to auto generate?

    I have used two different approaches to resolve a similar issue

    1) have a seperate table that holds the number you want to add to the field automatically, on you form use afterupdate event code with the dlookup function to look up the number in your seperate table and add it to the end of the user input.

    You will need to add a complete button to your form, add code to the form so the number in your table is increased by one ready for the next input. you may also want to add an exit button so if a user half completes an entry you can use code to delete in on exit.

    2) a clunky but simplified was is to also create a table to hold the number you want to add, use a macro on afterupdate event to add the new value and an update query on completion of the form to update the stored number, increasing it by one.

    the code to do this is pretty simple
    table to store your number
    [table_MyNumber]![Field_MyField]

    Replace table names with your own and add the correct code to the after update event property for your text box which should be bound to your table field holding the order number so it is updated when you update the form text box.

    Dim NewVariable as text
    Dim AutoNumber as text
    Dim Combined as text
    NewVariable = DLookup("[MyVariable_field]", "[table]")
    AutoNumber = DLookup("[Field_MyField]", "[table_MyNumber]")
    Combimbed = NewVariable & AutoNumber
    me.text1=Combined
    End Sub


    Simple is sometimes best

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by dg3447 View Post
    But when i tried to debug it shows Compile error:Method or data member not found and highlights Text_JobNumber.
    On which line (there are two references to Text_JobNumber in the code)? If you're using an old version of Access, it's possible that the Replace() function is not recognized (it was introduced in Access 2000 or Access 2003). Verify that the textbox controls are named Text_JobNumber and Text_Quantity. Apart from that I don't see what the problem can be. I tested the solution before posting it (both with Access 2003 and with Access 2010) and it worked.
    Have a nice day!

  13. #13
    Join Date
    Dec 2013
    Posts
    22

    No luck still

    Hi Sinndho,

    Sorry If I am being a bother.No luck still.But I am really at a difficult situation right now.Would you please be kind enough to take a look at my DB.I am sure I am doing something wrong.Please take a loot at it.

    Regards,
    Daryl
    Attached Files Attached Files

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You did not specify on which form in the database the problem occurs. Moreover there are no reference to objects named Text_JobNumber Text_Quantity or Command_AddJob in the database you posted in attachment.
    Have a nice day!

  15. #15
    Join Date
    Dec 2013
    Posts
    22
    Hi Sinndho,

    Sorry I posted the wrong attachment.Please see the attached file.
    Thank you for being kind enough to take a look at it.
    The form which I need the feature added is TASK DETAILS.
    The table where I need the form to be related to is TASKS.
    The queries are for filtered reports.
    Attached Files Attached Files
    Last edited by dg3447; 01-21-14 at 02:28. Reason: Wrong attachment

Posting Permissions

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