none
To store Data in different tables by selecting a value in one combo box RRS feed

  • Question

  • I have created an access database for society maintenance funding.
    Now I want a combo box such that when I select a year as 2016 in combo box I want to store all the related fields from form into "Maintenance 2016"
    and when I choose 2017 in combo box then i want to store all the data in "Maintenence 2017" table.

    "Maintenance 2016" contains fields as Flat No, Householder Names, Maintanance From, Maintenance To, Amount, And Deposite Date.
    and same fields for "Maintenance 2017"
    Friday, September 29, 2017 7:06 AM

All replies


  • "Maintenance 2016" contains fields as Flat No, Householder Names, Maintanance From, Maintenance To, Amount, And Deposite Date.
    and same fields for "Maintenance 2017"

    Hi krushan,

    Instead of making separate table per year, you can (better) use one table for all Maintenances, and use a query to extract the information for the different years:

        SELECT * FROM Maintenance_tbl WHERE Year([Deposite Date]) = ... (select the year form the combobox)

    Imb.

    Friday, September 29, 2017 9:27 AM
  • Hi krushan,

    Is your form bound or unbound? Do you mean you want to save the data from the form to the table? If so, you could try Append query.

    Sub InsertIntoX2() 
     
        Dim dbs As Database 
     
        ' Modify this line to include the path to Northwind 
        ' on your computer. 
        Set dbs = OpenDatabase("Northwind.mdb") 
         
        ' Create a new record in the Employees table. The  
        ' first name is Harry, the last name is Washington,  
        ' and the job title is Trainee. 
        dbs.Execute " INSERT INTO Employees " _ 
            & "(FirstName,LastName, Title) VALUES " _ 
            & "('Harry', 'Washington', 'Trainee');" 
             
        dbs.Close 
     
    End Sub 

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 2, 2017 3:18 AM
  • Hi Edward

    Thanks for replying

    But no I don't want this, I will explain in briefly

    I had Created 3 tables named ("Flat NO", "House holder Names", and "Maintenance').

    I had created a form named "Maintenance Form". Now the control Source Is "maintenance" table.

    In Maintenance Form, there are field Such as ("Flat NO", "Hoiseholder Name", "Category", "Maintenance Form", "Maintenance To", "Amount", and "Deposite Date")

    Now I want Flat No combobox to collect data from Flat No Table. Same way I want householder combobox to collect data from Householder table. (Now I want to bound both combo box such that when I select a value in Flat No combo box then automatically Householder combobox value appears).

    This I had done but only happens when I select a Flat no table or Householder name table. I am not able to get such link when I select a record source as Maintenance table.

    Now after entering all fields in maintenance form, I want them to store in Maintenance table.

    Monday, October 2, 2017 7:05 AM
  • Hi krushan,

    Could you share us your sample database? Your issue is more related with your Table Design and records.

    >>Now I want to bound both combo box such that when I select a value in Flat No combo box then automatically Householder combobox value appears

    For this, it seems you want to show Householder combobox by Flat No combo box. If so, you could change the data source of Householder combobox dynamically by Flat No combo box change event.

    >>Now after entering all fields in maintenance form, I want them to store in Maintenance table.

    For this, you want to store value in Maintenance table. If so, you could try Insert Into query with the value from maintenance form.

    Which is your requirement, or you have post multiple requirements in this one thread? What issue do you meet with my above suggestion?

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 3, 2017 6:08 AM