none
Add Mutliple records in a field on a form

    Question

  • Hello,

    I have an access database which contain a form for user to input the records. For example i have three fields called "Invoice No", "Request date" and "Requestor name". Incase if i have only one date and name for one invoice then i dont have any problem. But i have multiple records for single invoice number in that case how i can add it to a table .

    If i have multiple records then how it will add it into table as different records for each date and name or do i need to create another table to input these records.

    Kindly help me to solve this issue.


    VinWin06

    Wednesday, November 13, 2013 9:33 AM

Answers

  • Perhaps it'll help if you study the sample database that I uploaded (zipped) to my DropBox: https://www.dropbox.com/s/bet75y6phpcjc3y/Invoice.zip

    If you open the form frmInvoice in design view, you can see how the main form and subform have been designed. If you click once on the subform, and look at the Data tab of the Property Sheet, you'll see how the main form and subform have been linked.

    If you open frmInvoice in form view, you can enter data.

    Remark: you MUST enter an Invoice Number before you can enter records in the subform.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by vinwin1985 Wednesday, November 13, 2013 5:00 PM
    Wednesday, November 13, 2013 1:20 PM

All replies

  • I'd create one table that contains unique invoice numbers, with - if necessary - other fields that are unique to the invoice number, and another table in which the invoice number can be repeated, with fields that can vary for the same invoice number.

    For data entry, create a main form based on the first table, and a subform based on the second table, with the invoice number as link field.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 13, 2013 10:18 AM
  • Actually the main table will have unique no for invoice No, so how i can add this value to second table with same invoice no for multiple records.

    Also can you please explain more on your suggestion its help me for creating the same in my database.


    VinWin06

    Wednesday, November 13, 2013 10:20 AM
  • The table relationships could look like this:

    Field names are just examples, of course.

    And the form/subform:


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 13, 2013 10:39 AM
  • thanks for the reply. May i know how i can make this entry will update on the other table do i need to use any codes to update these values in table 2?

    VinWin06

    Wednesday, November 13, 2013 10:55 AM
  • Also help me on how i can add this two field into sub form because i have created sub form but i am not getting the add of new records.

    VinWin06

    Wednesday, November 13, 2013 11:21 AM
  • Perhaps it'll help if you study the sample database that I uploaded (zipped) to my DropBox: https://www.dropbox.com/s/bet75y6phpcjc3y/Invoice.zip

    If you open the form frmInvoice in design view, you can see how the main form and subform have been designed. If you click once on the subform, and look at the Data tab of the Property Sheet, you'll see how the main form and subform have been linked.

    If you open frmInvoice in form view, you can enter data.

    Remark: you MUST enter an Invoice Number before you can enter records in the subform.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by vinwin1985 Wednesday, November 13, 2013 5:00 PM
    Wednesday, November 13, 2013 1:20 PM
  • Thanks for your time in creating a database for me. If in case i want to create new invoice on the input form and its related records , because as per your example its showing add values to the existing records not the new one.

    VinWin06

    Wednesday, November 13, 2013 2:47 PM
  • You can create a new invoice in the main form, then create records in the subform under that new invoice.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 13, 2013 3:04 PM
  • I tried but i am getting an error that "the record cannot be deleted or changed because table invoice details includes related records". This when i try to enter new value on the invoice date.

    VinWin06

    Wednesday, November 13, 2013 3:39 PM
  • It looks like you are trying to change an existing invoice number. You can't create a new invoice number by changing an existing one. You have to move to a new, empty record and fill in the new invoice number there.

    Either click the 'New' button in the Records group of the ribbon (1), or the New Record button in the navigation buttons at the bottom of the main form (2). (Both will be disabled if you already happen to be on a new record).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 13, 2013 3:53 PM
  • Yeah thanks for this its works fine. i have a another question on the same field can i ask you in this question itself or do i need to create another one?

    VinWin06

    Wednesday, November 13, 2013 4:59 PM
  • If it's related you can ask it in this thread.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 13, 2013 5:02 PM
  • Thanks. Previously what i did was the request name and date are in the first table. I have another combo box called "category", based on this combo box if particular value is selected then these two request name and date should be mandatory.

    But now we have shown this field in second table now i can apply that particular criteria in this sub form. please find below the code i have used now how i can set the sub form field into this code.

    If Me.Combocategory = "US" Then
      If Len(Me.requestdate & vbNullString) = 0 Then
        MsgBox "You need to fill out the requestdate"
        Cancel = True
        Me.requestdate .SetFocus
      End If
    End If


    VinWin06

    Wednesday, November 13, 2013 7:25 PM
  • Is the Category field in the tblInvoice table or in the tblInvoiceDetails table?


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 13, 2013 8:39 PM
  • Tblinvoice table

    VinWin06

    Thursday, November 14, 2013 1:29 AM
  • You can use code like this in the Before Update event procedure of the subform:

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If Me.Parent!Combocategory = "US" Then
          If IsNull(Me.requestdate) Then
                MsgBox "You need to fill out the requestdate"
                Cancel = True
                Me.requestdate.SetFocus
            End If
        End If
    End Sub

    From the subform, Me.Parent refers to the main form.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 14, 2013 6:51 AM
  • Thanks for the code. But i have tested it but while i am selecting a particular category even if i am not filling anything on request date and name also i can able to save the record. But as per the code it should give the msg box message and also i am trying to set the tab order so that user can enter the data in subform once they have filled up the invoice NO but in this case i cannot able to find also these fields from subform also.

    Can you tell why it is not showing the message box.


    VinWin06


    • Edited by vinwin1985 Thursday, November 14, 2013 9:36 AM Additional question
    Thursday, November 14, 2013 9:02 AM
  • I'd need to see the database again.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 14, 2013 4:04 PM
  • OK please let me know once you have figure that out.

    VinWin06

    Thursday, November 14, 2013 8:13 PM
  • Sorry, I meant (but I didn't make that clear) that I'd need to see your current version of the database.

    Could you create a copy of the database, strip away everything that isn't relevant to the problem, as well as all sensitive information, then zip this copy and make it available through one of the websites that let you upload and share a file, such as Windows Live SkyDrive (https://skydrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Post a link to the uploaded and shared file in a reply here.

    Or post a message in the Access forum at www.eileenslounge.com. You can attach files up to 250 KB to a post there.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, November 14, 2013 9:39 PM
  • Hi Hans,

    Find the same database which you have created i have created another field called "Category" combo box and updated the code give by you in the before update procedure. 

    https://www.dropbox.com/s/8xouopsvqn1stn5/Invoice.zip


    VinWin06

    Friday, November 15, 2013 11:57 AM
    • The category table should have a primary key. You could add an AutoNumber field, but since category is only 2 characters, I used the Category field itself.
    • I renamed the category table to tblCategory for consistency.
    • There should be a relationship between tblCategory and tblInvoice on the Category field, with enforced referential integrity.
    • The Category combo box on frmInvoice should be bound to the Category field.
    • The first part of your Before Update event procedure for the subform won't work. You cannot cancel the update and set focus to the main form. If you cancel the update, focus remains on the current record of the subform. So I removed that part of the code.

    I have updated the database on my Dropbox: https://www.dropbox.com/s/bet75y6phpcjc3y/Invoice.zip


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 15, 2013 12:50 PM
  • I have tried using your database but still i can able to add records with out request date even i am selecting US on the category.

    VinWin06

    Friday, November 15, 2013 1:51 PM
  • I can't explain that. It works for me...


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 15, 2013 2:20 PM
  • Can you please tell when its work for you, like your moving to another record or trying to enter new record it works for you?

    even its work for me when i am trying to edit the existing record but i when i try to enter new record then i can able to skip that field.


    VinWin06


    • Edited by vinwin1985 Friday, November 15, 2013 3:42 PM additional info
    Friday, November 15, 2013 3:39 PM
  • Thanks Hans based on your code i have did some changes on my main form like after update and it will give msg box saying please fill up the date. So that user should know they have to fill that field. I have uploaded the database in drop box as well

    https://www.dropbox.com/s/8xouopsvqn1stn5/Invoice.zip

    Thanks you so much for your help.


    VinWin06

    Friday, November 15, 2013 3:59 PM
  • Whenever I try to move out of the record in the subform, whether using the mouse or the keyboard, the code fires if I have left request date empty and Category = "US".

    Are you sure that you have enabled macros when you opened the database? The easiest way to do that is to make the folder containing the database a trusted location for Access.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 15, 2013 4:23 PM
  • Can you please check my other idea how it looks with the database.

    VinWin06

    Friday, November 15, 2013 4:43 PM
  • That won't do what you want. The code only looks at the current record in the subform. Moreover, it won't prevent you from clearing the request date afterwards.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 15, 2013 5:53 PM
  • Thanks for the reply. Instead of only when request date is null we are giving the message can we able to set the focus directly when they selected "US" , then the cursor should come to request date . Because i have another validation need to be done on invoice date, so if cursor move directly to request date then its easy for me include the validation rule in invoice date as well.

    Just accept my ignorance , can we able to do it in this way?

    VinWin06


    • Edited by vinwin1985 Friday, November 15, 2013 7:24 PM addtnl
    Friday, November 15, 2013 7:22 PM
  • Again, that won't work. The subform may contain multiple records so setting focus to request date is not clear - you can't set focus to all subform records at the same time.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 15, 2013 8:06 PM
  • But not all records when user trying enter new record they have to fill up this request date bcoz I have other field which is dependent of this requestdate field. So how I can do this. Can you give some idea.

    VinWin06

    Friday, November 15, 2013 8:12 PM
  • Sorry, I don't understand your question.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 15, 2013 8:23 PM
  • Actually there is a invoice date field is should be later or equal from the later request date. So the user should input the request date once they have selected the category. Meaning after category field request date must be filled. Hope this clarifies you.

    VinWin06

    Friday, November 15, 2013 8:27 PM
  • That doesn't make sense to me. As I have mentioned two times before, there can be multiple records in the subform, each with a different request date. It's not clear to which record in the subform we should jump if "US" is selected as category. And even if we did jump to a specific record (the first record? the last record? a new record?), it wouldn't affect the other records in the subform.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 15, 2013 8:49 PM
  • Hope you understand my requirement . Can you please give some suggestion for me to do it. User input form 

    1. Invoice no

    2.category

    3.request date

    4request name

    5.invoice date 

    In this order onlyI i want my form to be working. Please give your suggestion.


    VinWin06

    Friday, November 15, 2013 8:58 PM
  • You could do this, but it doesn't make much sense to me:

    Private Sub Category_AfterUpdate()
        If Me.Category = "US" Then
            MsgBox "Please fill out the Request Date", vbExclamation
            Me.sbfInvoiceDetails.SetFocus
            Me.sbfInvoiceDetails!RequestDate.SetFocus
        End If
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 15, 2013 9:25 PM

  • I am getting run time error 2110 "Microsoft access can't move the focus to the control sbfinvoicedetails. while using the above codes.

    VinWin06

    Saturday, November 16, 2013 3:53 AM
  • can you suggest me some other suggestion based on your knowledge.

    VinWin06

    Saturday, November 16, 2013 4:00 AM
  • Once again, it works for me. I have updated the database on my DropBox.

    https://www.dropbox.com/s/bet75y6phpcjc3y/Invoice.zip


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2013 11:33 AM
  • even its working for me in my testing database given you. But in my original database its showing me this error. I am not sure what to do now.... :(

    VinWin06

    Saturday, November 16, 2013 1:42 PM
  • Did you perhaps leave some other code that causes the problem?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2013 2:41 PM
  • Actually i have another codes on "sub form before update" , "Category after update"  and "Invoice date one validation","Sub form load for maximize" and "Sub form for running current time". other than this i dont have any specific codes. But how its working on the testing one not here. i am totally confused.

    VinWin06

    Saturday, November 16, 2013 3:02 PM
  • Could you upload your database to Dropbox once more?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2013 4:23 PM
  • Sure let me upload and give you the main database itself.

    Here is the link for my main database

    https://www.dropbox.com/s/zgbv00zg46d9zai/Testing%20Db.zip


    VinWin06


    • Edited by vinwin1985 Saturday, November 16, 2013 4:32 PM DB upload
    Saturday, November 16, 2013 4:24 PM
  • First some general remarks:

    For lookup tables such as Category and User, it's best to create an AutoNumber field as Primary Key, and use that to link to other tables. Even if the description of a user or category changes, the autonumber value will remain the same. Moreover, a number field takes up less space than a text field.

    The Description field in the Buyer table is superfluous - it's just the concatenation of the Buyer and Department fields.

    The Department field in the Master_data table is superfluous - it follows from the buyer.

    The Name/tel field in the Master_data table is confusing. You probably don't need it if you store the name and extension in the appropriate table.

    Take a look at the design of the tables and at the Relationships window to see the changes.

    I modified the forms to work with the updated tables. The code works OK for me.

    See https://www.dropbox.com/s/0vty84tjf1ab73s/TestingDb.zip


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2013 6:21 PM
  • Thanks for your suggestion and spent time on my database design. Is there is any system problem on my side because when i selecting the category ID 2 then i am getting error "Microsoft access can't move the focus to the control ". I dont what do i need to change on my side. I am using office 2007 only .

    VinWin06

    Saturday, November 16, 2013 6:31 PM
  • But i like the way you have written your codes, its simply superb and amazing. May i know how i can develop my skills into writing this kind of codes. Definitely i know that it requires experience but may i know based on reading some can i get the level of knowledge.?

    VinWin06

    Saturday, November 16, 2013 6:34 PM
  • I don't understand why you get an error message when you select "PO Tender Multi-Source" as Category (i.e. CategoryID = 2). It works without error for me, and although I use Access 2010, I don't think that would cause the difference.

    Programming in Access is partly standard VBA, but of course you have to become familiar with the Access-specific aspects too. Some books that you might find useful:

    Access 2007 Programming By Example

    Access 2007 VBA Programmer's Reference

    Access 2007 VBA Programming For Dummies (don't be put off by the title)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2013 6:52 PM
  • Thanks for the books name. I am having all these books. I need to start reading it. Since its having huge number of pages sometimes I'm getting distracted. 

    For my datbase issue what we can do any idea bcoz still I don't understand it. Bcoz I remembered I have activated content before running the form. Still don't understand what its causing the problem.


    VinWin06

    Saturday, November 16, 2013 7:02 PM
  • I'm out of ideas for now, sorry.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Saturday, November 16, 2013 7:17 PM
  • OK in case if you got something please post it . At the mean time I will also look into it.

    VinWin06

    Saturday, November 16, 2013 7:21 PM
  • Hi Hans,

    Can you please tell me which version of access your using on your system, because yesterday i have tried with several options but still i am getting the same error. so that's don't know what to do on this issue...


    VinWin06

    Monday, November 18, 2013 2:20 AM
  • I'm using Access 2010 SP2.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 18, 2013 6:54 AM
  • but because of this version change does not impact my database know?

    VinWin06

    Monday, November 18, 2013 7:40 AM
  • It should work in all versions.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, November 18, 2013 12:21 PM
  • Finally i have removed the set focus codes then now its working fine focus need to be manually click by the user to the sub form. Also another issue i have faced required property in table preventing me to not get into the sub form so i have removed the required property also.

    Now i want to know is there is any vba code to make the field required at the end of user data entry. Like after they enter the data some code need to be triggered to check the required property.

    Is it possible to do?


    VinWin06

    Tuesday, November 19, 2013 12:52 PM
  • I fear not. Such code is usually placed in the Before Update event of the form. However, this event is fired when the user moves from the main form to the subform, so it would make it impossible to move to the subform, just like setting Required to Yes.

    I think you should rethink the flow of your application. Requiring the user to enter data in the subform before all data in the main form have been entered AND making fields on the main form obligatory is contradictory. I don't think you'll be able to get it working the way you want.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, November 19, 2013 2:04 PM
  • then how i can make it , because now i have changed the flow of the form as well like entering the subform data first but again i have problem that i need to enter the tendor_no so that i enter data in subform because both are linked to each other.

    Even if i entered the tendor_no first then other fields not allowing me to enter the sub form data.

    Can you please help me...


    VinWin06

    Wednesday, November 27, 2013 4:41 AM
  • Sorry, I have no idea.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, November 27, 2013 7:00 AM