Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation (A2K)

    Ok, I hate not being an access guru. Anyway this is what I need to do. It all has to do with Record management. Anyway I have a combo box that has a series name in it. Now each series has a certain retention period(i.e. 2yrs, 5yrs, etc). What I need to do is that when I click on one of the series another box should fill with the retention years set for the series. I am not sure how to do this, so any help would be appreciated. <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation (A2K)

    Not that I am a guru by a long shot, but ...

    Let's say the combo box is called Series and the textbox you want to fill is called Retention. Retention will have a ControlSource of:

    =DLookup("[RetentionField]","YourTable","YourTable.[SeriesField] = [Series]")

    This assumes that the Retention field is in another table (not bound to the form) and it also contains the SeriesField. If this isn't the case, please give a few more details.

    HTH

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation (A2K)

    The field I'm looking up is in the same table as the series name. There associated in that table. What other information would you like? I can try my best to explain what you need.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation (A2K)

    Is the textbox bound to a field somewhere? Or is this just for display purposes? I'm going to assume the latter. I've done this sort of thing by adding the column to the combobox and then using the column property of the combobox to set the control source for the textbox. here's a snippet from the online help:

    You can use the Column property to assign the contents of a combo box or list box to another control, such as a text box. For example, to set the ControlSource property of a text box to the value in the second column of a list box, you could use the following expression:

    =Forms!Customers!CompanyName.Column(1)


    Columns are 0 based collections so the first column is 0, the second 1 etc.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Automation (A2K)

    Since you have the series and retention fields in one table, why not use a combo box that reviews both sets of data?
    Regards,

    Gary
    (It's been a while!)

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation (A2K)

    I think that "There associated in that table" means they are two fields in the same record - correct? If this is not the table that the form is bound to then try the DLookup function or just put both fields in the combo box as other have suggested.

    If the form is bound to this table, I'm sorry but the purpose of the form isn't clear to me.

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Automation (A2K)

    One other question, is the series to retention a one to many relationship?

    If so, why not store the data in two tables. The first table stores the series names which links as a one to many to a second table that stores the series retention years.

    In either event, if all of the data is in one table,

    On a form, set the recordsource of the first combo box to a query that selects distinct series values (This will eliminate duplicates and return one value for each series.) Set the recordsource of a second combo box to a query that select all retention values from the table where the series equal the value of the series selected in the combo box. Have the form refresh on the on change event of both combo boxes.

    HTH
    Regards,

    Gary
    (It's been a while!)

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation (A2K)

    no it's only a one-to-one. The reason I need to pull it into a text box is because I need to do a mathmatical equation that will figure out the Review for Destruction Date. The form is not bound to the table the information if coming from. I will try the DLookup function and post back.

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation (A2K)

    ok I'm getting an error with my DLookup method. It shows #Error in the Retention text box here is what I have.

    =DLookUp("[RetentionField]","tblRecordMgmt","tblRecordMgmt.[RecSerNameField] = [RecSerName]")

    Retention being the field I am looking up to have placed in the textbox, and RecSerName being what is selected from the combo box which decides what retention looks up. Any help on what I'm doing wrong would be greatful.

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

    Re: Automation (A2K)

    You must either refer to the combo box as Forms![frmWhatever]![RecSerName] (where frmWhatever is the name of your form), or put it outside the quotes. So, either use

    =DLookUp("[RetentionField]","tblRecordMgmt","tblRecordMgmt.[RecSerNameField] = Forms![frmWhatever]![RecSerName]")

    or

    =DLookUp("[RetentionField]","tblRecordMgmt","tblRecordMgmt.[RecSerNameField] = '" & [RecSerName] & "'")

    Note the use of single and double quotes in the where-condition in the second expression (I have assumed that RecSerNameField is a text field):

    <pre>tblRecordMgmt.[RecSerNameField] = '" & [RecSerName] & "'"</pre>


  11. #11
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation (A2K)

    I tried it both ways. The first way I don't get an error until I start typing in data. Doesn't matter what data I type in it won'd work. Also when I select my selection from the combo box it still doesn't fill in the retention field, it jsut stays as the error. The other way gives me a ?#name type thing in the Retention text box. Not sure what that is. I have a couple idea's I'm going to try. Post again if there is something you may see wrong. HEre is the recent DLookup field.

    =DLookUp("[RetentionField]","tblRecordMgmt","tblRecordMgmt.[RecSerNameField] = Forms![frmAdditions]![RecSerName]")

  12. #12
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation (A2K)

    Thinking about this I don't think i am going to be able to use the DLookup feature. This being the reason. First I need that retention text to fill in a field in another table. Yes I need to lookup what the retention is from one table depending on the Record Series name, but when that gets pulled in I need it to write to another table where all the relevant data on that specific record is stored. So the text box is bound to something already and if my assumption is correct would make it so I can't use DLookup. Is there another way to pull that data into that field, or am I going to have to have a drop down for choices? I would like to have this automated because some people that enter data may not know the amount of retention on a record. Thanks for all the help I've recieved thus far.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automation (A2K)

    Try this instead:

    =DLookUp("[RetentionField]","tblRecordMgmt","[RecSerNameField] = " & chr(34) & [RecSerName] & chr(34))

    Once you're specified the Domain name in Dlookup ("tblRecordMgmt"), you don't use it again in the criteria expression. If the [RecSerName] refers to a control/field on your form, the take it out of the quotes or else use the full reference to the form. In some circumstances the full form reference works, but I have suspicions about its timing.

    If you need to update this calculated control, you would requery it in the AfterUpdate event of the combobox you're using to select the criteria. A #Name? error usually means you misspelled a field or control name.
    Charlotte

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Automation (A2K)

    Why are you filling in data in a table from a lookup? If you can look it up, you don't normally store it as well. If you have a bound control, you can't bind it to an expression, although you can use code to set the control's value to the result of the expression. That would allow you to use DLookup in code to retrieve the value and then simply do something like this:

    MyControl = strValueLookedup
    Charlotte

  15. #15
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation (A2K)

    I need to look it up and then store it to a different table for a couple of reasons. I am entering records management data for specific publications that come out. Now I have a table that holds the different types that publication can be, but it only holds certain information. SO what I need to do is just pull out some data from that table. The other data I am doing this just by using a combo box. For some reason my boss wants me to automate the Retention box so that it will fill in the retention years by itself, and then fill in the Date for Destruction review after that. So I need to figure out how to do this because my boss wants it this way and won't accept another way. Thus I must figure it out some how. I will try you suggestion in the previous post and thank you for the help you are providing. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

Page 1 of 2 12 LastLast

Posting Permissions

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