Automation (A2K)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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):

tblRecordMgmt.[RecSerNameField] = '" & [RecSerName] & "'"

Reply | Quote

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]”)

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Alright, I managed to talk my manager out of storing this data into another table. So now all I need to do is pull it into my retention box based on whats selected in the Combo box. They are related in the same table this is the DLookUp what I have now. Thanks for the help

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

Reply | Quote

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.

Reply | Quote