Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 423
  • Last Modified:

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
0
eldo64
Asked:
eldo64
  • 3
  • 2
  • 2
1 Solution
 
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
0
 
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?
0
 
jadedataMS Access Systems CreatorCommented:
Have you tried that theory yet?  It's looking like exactly what I thought it might be.
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
0
 
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
0
 
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
0
 
eldo64Author Commented:
Steve,

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

Chad
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now