Form Question: Store a ControlSource expression calculated value in RecordSource field

Hello all,

I have a data entry form where the user enters an AdHocID in a textbox and selects a location from a listbox.  This listbox contains an associated TreatmentTypeID.    I'd like to have another text box that shows a calculated SampleID = AdHocID + (100000)*(TreatmentTypeID) and then stores that calculated value in the SampleID field of the form's RecordSource.  I don't understand how to specify the calculated value to be stored in the SampleID field of the recordsource.  Am I missing something?  Is this even possible?  

Here's the expression I'm using in the form:

=[AHID]+(100000*(Forms![Ad Hoc Entry Sheet]![Location and TreatmentType IDs].Column(4))

Unfortunately this isn't even showing up correctly in the form and just returns #NAME?.  HELP!?!

Chad
eldo64Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jadedataMS Access Systems CreatorCommented:
Hey eldo64!

  =([AHID]+(100000*(Forms![Ad Hoc Entry Sheet]![Location and TreatmentType IDs].Column(4)))


  You need to work on shortening those form and control names.  There's no need for them to be that long.

  What data is in column 4, and are you really referring to the fourth column or column number 4 where the column numbers begin at zero.  If you are pointed at the wrong column this could be the issue.
regards
Jack
eldo64Author Commented:
Jack,

I hear you on the long names...sorry.  The form contains a listbox that selects LocationID.  Instead of the "Location and TreatmentType IDs" query, I have also used a SQL statement:

SELECT LocationID.LocationID, LocationID.TreatmentTypeName, LocationID.LocationName, LocationID.TreatmentTypeID FROM LocationID;

Here you can see that TreatmentTypeID is the fourth field.  After checking out some more help files, it looks like I should be using Column(3) since the column numbers begin at zero, but that doesn't seem to be the correct reference either.

What do you think?
jadedataMS Access Systems CreatorCommented:
Have you tried that theory yet?  It's looking like exactly what I thought it might be.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

serbachCommented:
Chad,

First, remove the calculation from the SampleID text box.

Second, I think that you should set the value for the SampleID text box in your listbox After Update event procedure. You didn't give the Name property for the listbox so I'll refer to it as lstYourList:

SampleID = AHID + 100000 * lstYourList.Column(3, lstYourList.ListIndex)

Then after you've selected the location in the lstYourList, the SampleID will be filled with the calculated value. The ListIndex is what you need to select the proper column AND row.

Sincerely,

Steve Erbach
Scientific Marketing
Neenah, WI
eldo64Author Commented:
Steve,

You're help is very much appreciated.  I'm having trouble getting the SampleID calulcation to work in the After Update event procedure.  Do I enter it as an expression?  Or as a macro?  I can't seem to get this After Update thing to work...

Chad
serbachCommented:
Chad,

The calculation should be entered as Code using the Code Builder. That is, select the List Box in question on your form (I called it lstYourList), then click on the Event tab in the List Box properties window, select the After Update event, click on the ellipsis button (...), and select Code Builder. This will open the VBA editor with the cursor positioned in between Private Sub lstYourList_AfterUpdate() and End Sub. That is where you place the calculation:

Private Sub lstYourList_AfterUpdate()
   SampleID = AHID + 100000 * lstYourList.Column(3, lstYourList.ListIndex)
End Sub

Of course you use the real name for the List Box that you've given it. If you haven't given the List Box a name (on the Other tab of the properties window) then you should do so to make any other coding easier. Then when you Run the form and change the value in the List Box, the After Update event will fire and will stuff the SampleID Text Box with the results of the calculation.

Does this help?

Sincerely,

Steve Erbach
Scientific Marketing
Neenah, WI

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eldo64Author Commented:
Steve,

Thanks for seeing me through to the final solution on that one!  Works great!

Chad
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.