I am currently working on a form in access for a hiring plan. Basically what it will do is you will enter a number in a text box and it will populate what site(out of 4) based on prioity and number of available seats. What I need it to do is to go to the next site and if there is a remainder fill it in also. The final step will be to give me dates that the classes can start. Any thoughts?
Private Sub txt_hiresneeded_AfterUpdate()
Dim db As Database
Dim frm As Form
Dim rec As Recordset
Dim mySQL As String
'Below are calculated values
Dim HireCapacity As Double
Dim StationCapacity As Double
Dim TrainingCapacity As Double
Dim OpenStations As Double
Dim SiteHires As Double
'These values are pulled from a table or form
Dim StatMultiplier As Double
Dim TtlStations As Double
Dim StationsInUse As Double
Dim TraineesPerClass As Double
Dim MaxClasses As Double
Dim HiresNeeded As Double
Set db = CurrentDb
Set frm = Forms!frm_HireCapacity
mySQL = "SELECT tbl_x65_sites.[Site Name], tbl_x65_sites.[Priority Code], tbl_x65_sites.[Stations], tbl_x65_sites.[Stations in use], tbl_x65_sites.[St Utiliz multiplier], tbl_x65_sites.[Class size limiter], tbl_x65_sites.[Training class limiter] FROM tbl_x65_sites WHERE (((tbl_x65_sites.[Priority Code])=1))"
Debug.Print mySQL
Set rec = db.OpenRecordset(mySQL)
StatMultiplier = rec![St Utiliz multiplier]
TtlStations = rec![Stations]
StationsInUse = rec![Stations in use]
TraineesPerClass = rec![Class size limiter]
MaxClasses = rec![Training Class limiter]
OpenStations = TtlStations - StationsInUse
StationCapacity = OpenStations * StatMultiplier
TrainingCapacity = TraineesPerClass * MaxClasses
If TrainingCapacity > StationCapacity = True Then HireCapacity = StationCapacity Else HireCapacity = TrainingCapacity
frm.txt_OneSite = rec![Site Name]
frm.txt_OneSiteCapacity = HireCapacity
HiresNeeded = frm.txt_hiresneeded
If HiresNeeded < HireCapacity = True Then SiteHires = HiresNeeded Else SiteHires = HireCapacity
frm.txt_OneSiteHires = SiteHires
frm.Requery
End Sub
Reply With Quote