Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access form to add to table. (2K)

    Hi,
    Quick query I hope.
    TableCounsel - ID(Autonumber),Name, Ethnicity,Gender, Chambers
    TableEthnicity - Ethnicity
    TblGender - Gender
    What I want to be able to do is add entries to TblCounsel but limit the list in comboboxes. This would be done in a form. Originally I tried adding the field Name to FrmNewCounsel(to add a new entry on TblCounsel) and then using comboboxes with data from TblEthnicity and TblGender to limit the user to specific values.
    There is also another table that I would like to create from TblCounsel using the data in the field Chambers. The problem with this data is that there are duplicate entries that I don't need. So the idea of going through and recording each entry and deleting the duplicates isn't very appealing. Is there a quick way to do this is Access?
    When the form had been set up with the field Name (from tblCounsel) and the comboboxes the database wasn't a happy bunny and didn't like the process. I had made sure that I set up the comboboxes to store the data in TblCounsel. i.e. CbxEthnicity to be stored in TblCounsel.Ethnicity. And so on..........
    Any help or direction would be appreciated.

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

    Re: Access form to add to table. (2K)

    I feel confused about your description.

    In the first place: you can create a query based on TableCounsel, and add only the Chambers field to the query grid. Set the Unique Values property of the query to Yes. Switch to datasheet view to check that the query returns the list of Chambers, without duplicates. then switch back to design view, and select Query | Make-Table Query... Specify a name for the new table, for example tblChambers, and click OK. Then run the query (Query | Run...) You should now have a table with the unique values of Chambers that can be used as row source for a combo box.

    To enter counsels, you need a form based on TableCounsel, with a text box bound to Name, and combo boxes bound to Ethnicity, Gender and Chambers. That is, the Record Source property of the combo boxes should refer to a field in TableCounsel, *not* to a field in one of the auxiliary tables. The Row Source property of each combo box should contain the name of corresponding auxiliary table. If you want to prevent the user from entering values that are not in the auxiliary table, set the Limit To List property of the combo boxes to No.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access form to add to table. (2K)

    Hi Hans,
    If I understand your advice, the record source for the new form would be based on TblCounsel with the Row source being based on the query or tables that hold the data. This will work fine and is what I was trying to achieve, although somewhat incorrectly..........

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

    Re: Access form to add to table. (2K)

    The form has a Record Source. For your form, the Record Source is TblCounsel.

    Many controls on the form, such as text boxes, combo boxes and check boxes, have a Control Source. For bound controls, the Record Source is the name of a field from the Record Source. (Other types are calculated controls, whose Control Source is a formula starting with =, and unbound controls, whose Record Source is blank.)

    Combo boxes and list boxes also have a Row Source that determines which items are displayed in the list. If the Row Source Type is Table/Query, the Row Source is the name of a table or query (or an SQL statement). Your form will contain three combo boxes:

    <table border=1><td align=center>Name</td><td align=center>Control Source</td><td align=center>Row Source</td><td>cbxEthnicity</td><td>Ethnicity</td><td>tblEthnicity</td><td>cbxGender</td><td>Gender</td><td>tblGender</td><td>cbxChambers</td><td>Chambers</td><td>tblChambers</td></table>

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access form to add to table. (2K)

    Thanks Hans,
    I've completed the form now and it more or less works smoothly. The only problem I'm having is with a combo box that gathers data from TblCounsel so that the information is displayed on the form, i.e. lists all Counsel within the combobox and when clicked on displays the entry on this form. This is so that the user can search for previous entries and amend them. It comes up with 'Can't find Project or Library', any ideas?
    I also have various reports with a text box totalling the total amount of payments sent or outstanding. These reports list all entries that correspond to certain values. I would like to produce a central report that doesn't list the entries but just the total. i.e. in ReportActual there is a textbox that has calculated the total payment for that report, for arguments sake its Text23.
    I've tried adding =([ReportActual.Text23]) to this new Report (in a new text box) to get the value needed from RptActual but it doesn't work. I basically want a summary of all the totals from all the reports without detailing the actual entries. Am I doing something wrong?
    Hope that makes sense.

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

    Re: Access form to add to table. (2K)

    1. What is the code behind the combo box? You probably have an After Update procedure for the combo box. Could you post it?

    2. You can't refer to a text box on another report. You will have to design a Totals query that calculates the summary data, and use that as record source for your summary report.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access form to add to table. (2K)

    Hi Hans,
    This is the coding for the combobox.

    Private Sub Combo18_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[AdvocateID] = " & Str(Me![Combo18])
    Me.Bookmark = rs.Bookmark
    End Sub

    I've placed the relevant tables, etc in the attachment. Obviously there will some errors due to the rest of the database being missing.
    Attached Files Attached Files

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

    Re: Access form to add to table. (2K)

    It is not very helpful that your form does not contain the relevant combo box. It does contain orphaned code for three non-existing combo boxes, however; this code is OK in itself.

    Your Form_Load procedure contains DocCmd instead of DoCmd. That may well be the cause of the error.

Posting Permissions

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