Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Red face Unanswered: How to automatically populate data?

    Please could somebody help me with the following.

    I have the following 5 Tables and 5 related Forms:-

    1. Owner Table (this data is used to create an Owner Form)
    Including Owner ID etc.

    2. Property Table (this data is used to create a Property Form)
    Including Property ID etc.
    ***The property Form includes a ComboBox to choose which Owner ID owns the property.

    3. Renter Table (this data is used to create a Renter Form)
    Including Renter ID etc.

    4. Contract Table (this data is used to create a Contract Form)
    Including Contract ID etc.
    ***The Contract Form includes two ComboBoxes to choose which PropertyID and which Renter ID will create the Contract together.

    5. Rental Income Table (this data is used to create the Rental Income Form)
    ***This Form is used to input rental income received.

    My problem is that I would like to be able to have a ComboBox where I can select the Contract Number. When the Contract Number has been selected, I want to have 3 Fields which automatically show the Property ID, Owner ID and Renter ID for that particular Contract.

    Please would somebody advise how can I automatically show the Property ID, Owner ID and Renter ID on the Contract Form?
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    Nov 2009
    Posts
    223
    I have attached a Sample Database which I hope will be useful?

    I have also tried to populate the Fields using Relationships (not sure if this is the easiest way to do this) and consider it will be ME who has to resolve any technical issues in the future - and I need to understand how it works? And I am still a relative beginner...
    Attached Files Attached Files
    What would you attempt to do if you knew you would not fail?

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can change the RowSource of the combo so that it includes all the wanted data in several columns.

    1. Create a query Qry_Contracts with the SQL statement (see also attached picture):
    Code:
    SELECT [Contract Table].[Contract ID], 
           [Rental Income Table].[Property ID], 
           [Rental Income Table].[Owner ID], 
           [Rental Income Table].[Renter ID]
      FROM [Contract Table] 
    LEFT JOIN [Rental Income Table] ON [Contract Table].[Contract ID] = [Rental Income Table].[Contract ID];
    2. Change the combo:
    Code:
    Row Source: Qry_Contracts
    Column count: 4
    Column widths: 1cm;0cm;0cm;0cm (it should not be too hard to convert in inches if necessary)
    After Update: [Event Procedure]
    3. The event handler for the AfterUpdate event of the combo is:
    Code:
    Private Sub Contract_ID_AfterUpdate()
    
        Me.Property_ID.Value = Me.Contract_ID.Column(1)
        Me.Owner_ID.Value = Me.Contract_ID.Column(2)
        Me.Renter_ID.Value = Me.Contract_ID.Column(3)
        
    End Sub
    However I don't understand the logic behind the database schema. To me it should be the the table Contract Table that should hold the foreign keys to the other tables, but I might be wrong.
    Attached Thumbnails Attached Thumbnails Qry_Contracts.jpg  
    Have a nice day!

  4. #4
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    You are the King of Microsoft Access - I am sure Bill Gates will offer you a job if he knew how good you were?

    I am at an important stage in my database building and this problem has been giving me a headache for some time now. Thank you for guiding me along and for showing me how to automatically populate those fields.

    After reading your code, I nearly understand maybe 80% of it. Thanks a lot.

    Thanks also for your comment regarding the logic of the DB. Having thbought about it last night, I think you are correct when you say it may be better to have the Contract Table as the main source. Upon reflection, this woudl also be easier for me to "link" that esential information to any other Forms or Reports in the future.

    Can I assume that Relationships have nothing to do with this new coding?
    What would you attempt to do if you knew you would not fail?

  5. #5
    Join Date
    Nov 2009
    Posts
    223
    I am going to attempt to write the code myself for the Contracts Table to be the main source. Hopefully it will not take me more than a few days (haha) but I shoud learn something in the process?

    Currently my Contract Table/Form has

    Contract ID (autonumber)
    Property ID (manual combobox choice)
    Owner ID (auto populated) from Query code =[Property ID].Column(6) coz the Property ID is linked to OwnerID on the Property Form
    Renter ID (manual combobox choice)

    Mmmm, having looked at this, I could have all the information already? Nah, I think it still needs some adjustment to make it efficient??
    Last edited by reddevil1; 09-04-11 at 08:33.
    What would you attempt to do if you knew you would not fail?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by reddevil1 View Post
    You are the King of Microsoft Access
    No, I'm not, but you're welcome anyway!

    Quote Originally Posted by reddevil1 View Post
    Can I assume that Relationships have nothing to do with this new coding?
    Relationships are not necessary for this to work. However both proceed from the same logic.
    Have a nice day!

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Mmm, I had a plan.....which is not going to plan

    Currently my Contract Table/Form has

    1. Contract ID (autonumber)

    2. Property ID (manual combobox choice) from "Find Property Query"
    Code:
    SELECT [Property Table].[Property ID], [Property Table].[Apartment Number], [Property Table].[Building Name], [Property Table].[Street Name], [Property Table].[Town Name], [Property Table].[City Name], [Property Table].[Owner ID]
    FROM [Property Table]
    ORDER BY [Property Table].[Property ID];
    3.Owner ID (auto populated) from Query code =[Property ID].Column(6)

    4. Renter ID (manual combobox choice)


    My question is...

    on the Rental Income Form, I want to have a ComboBox with a dropdown choice of Contract ID (I can do that with a simple Query) which will then automatically populate the PropertyID, Owner ID and Renter ID fields.

    Do I need to create a Query to do this (my Query did not show the information from the automatically populated Owner ID Field)? Or is it more efficient to call the data from each Table through a Relationship?

    I have had a few days rest and now need to fill my brain up again - sorry for the stupid questions!!
    Last edited by reddevil1; 09-04-11 at 09:13.
    What would you attempt to do if you knew you would not fail?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    - Set the RowSource property of the ComboBox to:
    Code:
    SELECT [Property ID], [Owner ID],[Renter ID] FROM [Property Table]
    ORDER BY [Property ID];
    - Assign the value 3 to the ColumnCount property of the combo.

    - Set the proper width in the ColumnWidths property (remember that this only concerns the drop-down list part of the combo: the textbox part can only display a single value (i.e. the value from one column).

    - Set the value of the BoundColumn property to determine which column of the list will be displayed in the TextBox part of the combo.

    Note: For the BoundColumn property the first column = 1, the second = 2, etc. while in general the first column is addressed as Column(0), the second is Column(1), etc. This can be confusing. If you assign 0 (zero) to this property the textbox part of the combo will display the ordinal position of the row selected into the list, not the value from a column.
    Have a nice day!

  9. #9
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Thanks again for your continued support.

    I have tried to follow your advice and I have created a Sample database which is ALMOST as I would like it? It just has ONE Field which is not automatically populating (the Owner ID Field).

    I wonder if any comments could be made on the following:-

    1. I thought that whenever I obtain any "ID" in a Field, then it would be easy to obtain information from all other Fields in that Table? eg. if i got the OwnerID, then i could easily get the Owner Name, Owner Address, Owner Phone from the same Table. I thought I can do this be using Relationships??

    So, I have attempted to create Relationships, making the “Contract Table” the main source (coz it is the only Table which contains Contract ID, Owner ID, Property ID and Renter ID) and all the other Tables.

    Have I created the Relationships based on sound strategy? Are the Links to other ID Fields reasonable??

    2. Why doesn’t the Owner ID Field show in the Contract Table or the Contract Table Query? But it does show on the Contract Table Form?

    3. How can I get the Owner ID to show?

    This is the last of my Form problems (I think) and then I am off to create loadsa Reports (whehey).
    Attached Files Attached Files
    What would you attempt to do if you knew you would not fail?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Relationships do not automatically retrieve data into a form or a report.

    From Microsoft's "Guide to table relationships" (http://office.microsoft.com/en-us/ac...0120534.aspx):
    There are several reasons why you should create table relationships before you create other database objects, such as forms, queries and reports.
    -Table relationships inform your query designs
    -Table relationships inform your form and report designs
    -Table relationships are the foundation upon which you can enforce referential integrity to help prevent orphan records in your database. An orphan record is a record with a reference to another record that does not exist
    (for example, an order record that references a customer record that does not exist).
    To insert data from a table into a control (usually a TextBox) of a form, there are several methods. The most common are:

    1. Bind the form to the table or to a query that comprises the column of the table where the data is stored (data source) by using its RecordSource property, then assign the name of the desired column (or its alias) to the ControlSource property of the control.

    2. In the class module of the form, create a Recordset and open it to the table or to a query that comprises the column of the table where the data is stored. You can then use:
    Code:
    Me.Control1.Value = MyRecordSet!ColumnName
    Note: When you bind a form to a data source a RecordSet is created automatically and is accessible through the Recordset property of the form.

    3. Use a Domain Function (usually DLookUp) to retrieve a single value from a data source:
    Code:
    Me.Control1.Value = DLookUp("Client_Name", "Tbl_Client", "Client_Id = 129")
    4. Use a ComboBox or a ListBox to retrieve a data set and "pickup" the desired data from this control:
    Code:
    Me.Control1.Value = Me.Combo1.Column(2, 7)
    Have a nice day!

Posting Permissions

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