Value of TextBox based on ComboBox

[John V]

Ok, so the saga of my (Work)Calls form continues.

I am now able to pick Positions based on the Employer for the chosen Event (Thanks John G and June 7) and I have even managed to add an unbound TextBox to show the Department the chosen position is associated with.

What I would like to have now is an unbound TextBox that will show the (Pay)Rate of the position based on the date of the work call.

The date of the work call is important because the rates change on a yearly basis.

I have a table where I keep track of the rates (tblRates) and these rates are entered through a subform (sbfrmRates) of a form (frmPositions) to enter information about each Position. The Positions form has tblPositions as its recordsource.

The fields in the Rates Table (tbleRates) are:

RateID (PK, Autonumber)
Rate (Currency)
StartDate (Date/Time)
EndDate (Date/Time)
PositionFK (Number)


The fields in the Positions Table (tblPositions) are

PositionID (PK, Autonumber)
Position (Text)
Department (Text)
EmployerFK (Number)


I would like the unbound Rate Textbox (txtRate) to display the appropriate rate based on the following criteria.

I need to pick the correct Rate for the chosen Position based on the Call Date. To do this I need to look at the Rates Table (tblRates) and pick the Rate, for the chosen Position, that has a StartDate <= CallDate AND EndDate >= CallDate

I have tried various things but think I probably need to use DLookup somehow because the Rate TextBox (txtRate) gets its value from the Rates Table (tblRates) and the recordsource for the Calls Form (frmCalls) where the Rate TextBox resides, is the Calls Table (tbleCalls).

I cannot for the life of me figure out the correct arguments for the Dlookup Function. I have a feeling it should be something like this:

Field Name: Rate
Table Name: tblRates
Criteria: ???

I have entered the following code:


Private Sub cboPosition_AfterUpdate()

'Pick Rate for chosen Position based on Call Date and enter it in Rate TextBox

Dim Rate As Variant
Dim sqlRate As String
sqlRate = "SELECT tblRates.Rate " & _
"FROM tblRates " & _
"WHERE tblRate.PositionFK = " & Me![cboPosition].Column(0) & " " & _
"AND tblRates.StartDate <= " & Me![cboDate] & " " & _
"AND tblRates.EndDate >= " & Me![cboDate] & " "
Rate = DLookup("Rate", "tblRates", "Rate='sqlRate'")
Me![txtRate] = Rate

Stumped (once again).

John V

[ItsMe]

You cannot use an entire SELECT query as the criteria in a Domain Function. You are correct that you need a SELECT query to retrieve the RateID. I am not seeing another way because of the not typical approach of having a Start Date and End Date on the same record.

What does the Rowsource of your combo look like?

[John V]

The rowsource of my combobox is select query in vba that is triggered by an AfterUpdate event for another combobox.This is something that we discussed in another thread earlier this week.

Rather than describing all of the details of the form I have attached a copy of my database.

John V

Income.zip

[ItsMe]

Sorry to ask such difficult questions. I was able to find out what your combo's Row Source is by downloading the file, unzipping it, determining the correct form to look at, determining the name of the combo in question, and searching the VBA to locate the following

sqlPosition = "SELECT tblPositions.PositionID, tblPositions.Position, tblPositions.Department, tblPositions.EmployerFK " & _
"FROM tblPositions " & _
"WHERE tblPositions.EmployerFK = " & Me![cboEvent].Column(4) & ""

What this tells me is what I suspected when I asked for the Row Source of the combo. The data you are trying to retrieve using the DLookup is already in the Combo.

So
Me.txtRate = Me.cboPosition.Column (0)

[John V]

Sorry, I didn't mean to make it so much work to find your way through my database. I apologize for this. In the future I will give a little detail of where to go and what to do. Oops!

Anyway, your suggestion while it does produce a result without error it is not the result I am looking for.

To illustrate what I am trying to accomplish consider these examples.
If I work a call on Wickedon July 11,2014 as the Department Head of the Audio department I would like it to tell me the pay Rate is $28.92. On the other hand if I work on Walking with Dinosaurs on Janury 9,2015 as the Department Head of the Audio department I would like it to tell me the pay Rate is $30.02.

You can find the rates I am quoting on the Rates subform of the Positions form. These values are recorded in the Rates Table (tblRates).

The Rate is not information that is derivable from the information queried in the position combobox. Setting txtRate equal to the Position Combobox, Column (0) puts the PositionID in the Rate Textbox. The PositionID is a number that is of no concern to me (or any user) it is only for the database's use.

I need it to pick the Rate that is "in play" for the Position on the Date of the Call and display it in the Rate Textbox.

I have attached another copy of my database with the data I have quoted in this post.

Any help is appreciated.
John V

Income.zip

[ItsMe]

Hmmmm, not remembering helping you with this DB before? I do remember someone asking about pay rates and they had a table set up similar to yours. I think the question I asked them was if there were any dates not included in their table. For instance, there is not a pay rate for February 14, 2013.

What happens is that you have two dates in the same record/row. Comparing two dates within the same row is not impossible but not always practical. For your scenario, it is impractical. You need to consider the Business Rules first. Unfortunately, business rules are not always kind to developers and rules of Normalization.


The easiest way to store your business rules in a DB is if the business rules have no chance of changing. So lets take that as an example.

Business Rule: Every calendar year the pay scale is evaluated and a rate is determined for each Job Title and its respective Department.

For this rule, your rates table only needs to have a calendar year, not a full date.

Instead of

RateID	Rate	StartDate	EndDate	PositionFK
1	$25.62	01-Jan-14	31-Dec-14	1
2	$26.00	01-Jan-15	31-Dec-15	1
3	$20.74	01-Jan-14	31-Dec-14	2
4	$21.00	01-Jan-15	31-Dec-15	2
5	$28.96	01-Jan-14	31-Dec-14	3
6	$30.02	01-Jan-15	31-Dec-15	3
7	$25.52	01-Jan-14	31-Dec-14	4
8	$26.00	01-Jan-15	31-Dec-15	4
9	$20.74	01-Jan-14	31-Dec-14	5
10	$21.00	01-Jan-15	31-Jan-15	5

You could have

RateID	Rate	ScalePeriod	PositionFK
1	$25.62	2014	1
2	$26.00	2015	1
3	$20.74	2014	2
4	$21.00	2015	2
5	$28.96	2014	3
6	$30.02	2015	3
7	$25.52	2014	4
8	$26.00	2015	4
9	$20.74	2014	5
10	$21.00	2015	5

With this, your Primary Key will be more easily retrieved.

[June7]

I think ItsMe is suggesting you save the RateID of the Rate record instead of the rate itself. The combobox RowSource does not have the RateID available, hence the DLookup approach. But the syntax is wrong. ItsMe's suggested table revision would simplify the lookup although it could be done with the two date fields using BETWEEN AND operator or >= and <= operators.

DLookup("Rate", "tblRates", "PositionFK=" & Me.cboPosition.Column(3) & " AND ScalePeriod=" & Year(Me.CallDate))

Since the rate is dependent on 2 inputs on form (CallDate and PositionFK), changing either could impact the rate. Probably should put the code in the form BeforeUpdate event.

However, saving calculated data has risk of data getting 'out-of-sync' (note previous statement). It is not actually necessary to save the Rate or RateID. Alternative is to retrieve the rate in query that joins a query of tblCalls with tblRates (if modified as ItsMe suggests) with compound link on ScalePeriod and PositionFK.

[John V]

What is probably not apparent from my sample data is that the dates are not consistent across employers. For testing purposes I am just making up data. In actual fact the dates for the change in rate may occur will not be a calendar year. These rates are negotiated union agreements that may have data like this:

RateID	Rate	StartDate	EndDate	PositionFK
1	$25.62	01-Jul-14	30-Jun-15	1
2	$26.00	01-Jul-15	30-Jun-16	1
3	$20.74	01-Jul-14	30-Jun-15	2
4	$21.00	01-Jul-15	30-Jun-16	2
5	$28.96	01-Apr-14	31-Mar-15	3
6	$30.02	01-Apr-15	31-Mar-16	3
7	$25.52	01-Jul-14	30-Jun-15	4
8	$26.00	01-Jul-15	30-Jun-16	4
9	$20.74	01-Jul-14	30-Jun-15	5
10	$21.00	01-Jul-15	30-Jun-16	5

Still other rate increases might in fact fall nicely on the calendar year.

As well if agreements are signed with new employers the rates may only be locked in for 5 months at first and then 1 year increments after that to get them to align with the fiscal year of the employer.

There has to be a way to pick the rate on a given date if that date falls between two dates whose values are stored in a table.

John V

[June7]

There is. I was posting at same time as you. Review my previous post.

[John V]

Thank you June7.

I may be misunderstanding, but, I believe your are think I am wanting to store the Rate in my Calls Table (tblCalls). That is not my intention. This textbox (txtRate) is unbound. I am just wanting it to appear for informational purposes only. The Rate is already stored in the Rates Table (tblRAtes) there is no need to store it again (is there?)


Could I do something like:

In the AfterUpdate of the Position Combobox (cboPosition)

DLookup("Rate", "tblRates", "PositionFK=" & Me.cboPosition.Column(3) & " AND "StartDate<=" & Me.cboCallDate & " AND "EndDate>=" & Me.cboCallDate & ")

If this DLookup function will work how do I then tell access to put the Dlookup result in the Rate Textbox (txtRate)?


John V

[June7]

If you don't save the rate (or RateID) do you intend to retrieve it in report using DLookup to calculate charges?

Me.txtRate = DLookup("Rate", "tblRates", "PositionFK=" & Me.cboPosition.Column(3) & " AND #" & Me.cboCallDate & "# BETWEEN StartDate AND EndDate")

[ItsMe]

I wonder if a table for contracts and a table for addendums is in order.

I am sensing trouble down the road trying to keep track of date ranges. Not sure I know enough about how operations dictates the work flow. I am imagining a user trying to maintain the date ranges and their associations with labor type and locations.

[John V]

Thanks June7 this works great.

Yes that is my intention (eventually) for the report. Is that bad?

John V

[June7]

Not bad per se, but domain aggregate functions can be slow performers in queries and on forms or reports.