Priority in a form

[r10701]

I have code in a form that loops based on priority that is selected on combo boxes. Those boxes are linked to a simple table that has priority 1-5. What I am trying to do is to make an option to not consider a site as a priority. I basically want to make the priority 0 and skip that site. This code includes some date slection and other things. I'm just not sure where and how to make it consider a 0 priority.

Private Sub Command32_Click()

Dim db As Database
Dim frm As Form
Dim rec As Recordset
Dim mySQL As String

'These values are pulled from a table or form

Dim HiresNeeded As Double '# of guides to hire per week
Dim StartDate As Double 'start date of hiring time frame
Dim EndDate As Double 'end date of hiring time frame
Dim Site As String
Dim Priority As Double
Dim LoopDate As Date
Dim StatCap As Double 'number of stations available in the call center
Dim Util As Double 'stations currently in use
Dim StUtil As Double '# of guides that can be assigned to a station
Dim SzLimit As Double 'Size new hire class is limited to

'Calculated Values
Dim HireCap As Double '# of guides have seats to hiring
Dim HireAbility As Double '# of guides can hire given class size limit
Dim ToHire As Double '# of guides to hire on a given date in a given site


DoCmd.SetWarnings no
DoCmd.RunSQL "UPDATE tblResults SET tblResults.[Indianapolis Hire] = 0, tblResults.[Saint John Hire] = 0, tblResults.[Mexico Hire] = 0, tblResults.[Cork Hire] = 0, tblResults.[Manila Hire] = 0, tblResults.[Indianapolis Cap] = 0, tblResults.[Saint John Cap] = 0, tblResults.[Mexico Cap] = 0, tblResults.[Cork Cap] = 0, tblResults.[Manila Cap] = 0, tblResults.[Hires Needed] = 0, tblResults.[Remainder]= 0"

Set db = CurrentDb
Set frm = Forms!FrmStaffingModel_copy

StartDate = frm.txtStartDate
EndDate = frm.EndDate

'Update tblSiteData with priority data entered on form

mySQL = "SELECT tblSiteData.Site, tblSiteData.[Seating Capacity], tblSiteData.[Station Utiliz], tblSiteData.[Class Size limiter], tblSiteData.Priority FROM tblSiteData WHERE (((tblSiteData.Site)='Indy'))"
Set rec = db.OpenRecordset(mySQL)
rec.Edit
rec!Priority = txtIndypriority
rec.Update
rec.Close

mySQL = "SELECT tblSiteData.Site, tblSiteData.[Seating Capacity], tblSiteData.[Station Utiliz], tblSiteData.[Class Size limiter], tblSiteData.Priority FROM tblSiteData WHERE (((tblSiteData.Site)='Saint John'))"
Set rec = db.OpenRecordset(mySQL)
rec.Edit
rec!Priority = txtSaintJohnpriority
rec.Update
rec.Close

mySQL = "SELECT tblSiteData.Site, tblSiteData.[Seating Capacity], tblSiteData.[Station Utiliz], tblSiteData.[Class Size limiter], tblSiteData.Priority FROM tblSiteData WHERE (((tblSiteData.Site)='Mexico'))"
Set rec = db.OpenRecordset(mySQL)
rec.Edit
rec!Priority = txtMexicopriority
rec.Update
rec.Close

mySQL = "SELECT tblSiteData.Site, tblSiteData.[Seating Capacity], tblSiteData.[Station Utiliz], tblSiteData.[Class Size limiter], tblSiteData.Priority FROM tblSiteData WHERE (((tblSiteData.Site)='Cork'))"
Set rec = db.OpenRecordset(mySQL)
rec.Edit
rec!Priority = txtCorkPriority
rec.Update
rec.Close

mySQL = "SELECT tblSiteData.Site, tblSiteData.[Seating Capacity], tblSiteData.[Station Utiliz], tblSiteData.[Class Size limiter], tblSiteData.Priority FROM tblSiteData WHERE (((tblSiteData.Site)='Manila'))"
Set rec = db.OpenRecordset(mySQL)
rec.Edit
rec!Priority = txtManilaPriority
rec.Update
rec.Close

'Begin Calculations

LoopDate = StartDate
Do
mySQL = "SELECT Sheet1_x65.Date, Sheet1_x65.HiresNeeded FROM Sheet1_x65 WHERE (((Sheet1_x65.Date)=#" & LoopDate & "#))"
Set rec = db.OpenRecordset(mySQL)
HiresNeeded = rec!HiresNeeded
rec.Close

mySQL = "SELECT tblResults.Week, tblResults.[Hires Needed] FROM tblResults WHERE (((tblResults.Week)=#" & LoopDate & "#))"
Set rec = db.OpenRecordset(mySQL)
rec.Edit
rec![Hires Needed] = HiresNeeded
rec.Update
rec.Close


Priority = 1
Do
mySQL = "SELECT tblSiteData.Site, tblSiteData.[Seating Capacity], tblSiteData.[Station Utiliz], tblSiteData.[Class Size limiter], tblSiteData.Priority FROM tblSiteData WHERE (((tblSiteData.Priority)=" & Priority & "))"
Set rec = db.OpenRecordset(mySQL)

StatCap = rec![Seating Capacity]
StUtil = rec![Station Utiliz]
SzLimit = rec![Class Size limiter]
Site = rec![Site]
rec.Close

'***Will need to add option for varying date!***
mySQL = "SELECT Sheet1_x65.date, Sheet1_x65.cork, Sheet1_x65.indy, Sheet1_x65.saj, Sheet1_x65.mex, Sheet1_x65.manila FROM Sheet1_x65 WHERE (((Sheet1_x65.date)=#" & LoopDate & "#))"
Debug.Print mySQL
Set rec = db.OpenRecordset(mySQL)
Select Case Site
Case "Indy"
Util = rec![indy]
Case "Saint John"
Util = rec![saj]
Case "Mexico"
Util = rec![mex]
Case "Cork"
Util = rec![Cork]
Case "Manila"
Util = rec![manila]
End Select

HireCap = (StatCap * StUtil) - Util

If HireCap > 0 Then
If HireCap > SzLimit Then HireAbility = SzLimit Else HireAbility = HireCap
ElseIf HireCap <= 0 Then HireAbility = 0
End If

If HiresNeeded > 0 Then
If HireAbility < HiresNeeded Then ToHire = HireAbility Else ToHire = HiresNeeded
ElseIf HiresNeeded <= 0 Then ToHire = 0
End If


'***Need to add date varying capability
mySQL = "SELECT tblResults.Week, tblResults.[Indianapolis Cap], tblResults.[Saint John Cap], tblResults.[Mexico Cap], tblResults.[Manila Cap], tblResults.[Cork Cap] FROM tblResults WHERE (((tblResults.[Week])=#" & LoopDate & "#))"
Set rec = db.OpenRecordset(mySQL)
rec.Edit
Select Case Site
Case "Indy"
rec![Indianapolis Cap] = HireAbility
Case "Saint John"
rec![Saint John Cap] = HireAbility
Case "Mexico"
rec![Mexico Cap] = HireAbility
Case "Cork"
rec![Cork Cap] = HireAbility
Case "Manila"
rec![Manila Cap] = HireAbility
End Select
rec.Update
rec.Close


mySQL = "SELECT tblResults.Week, tblResults.[Indianapolis Hire], tblResults.[Saint John Hire], tblResults.[Mexico Hire], tblResults.[Manila Hire], tblResults.[Cork Hire] FROM tblResults WHERE (((tblResults.[Week])=#" & LoopDate & "#))"
Set rec = db.OpenRecordset(mySQL)
rec.Edit
Select Case Site
Case "Indy"
rec![Indianapolis Hire] = ToHire
Case "Saint John"
rec![Saint John Hire] = ToHire
Case "Mexico"
rec![Mexico Hire] = ToHire
Case "Cork"
rec![Cork Hire] = ToHire
Case "Manila"
rec![Manila Hire] = ToHire
End Select
rec.Update
rec.Close

HiresNeeded = HiresNeeded - ToHire
Priority = Priority + 1

Loop Until Priority = 6

mySQL = "SELECT tblResults.Week, tblResults.Remainder FROM tblResults WHERE (((tblResults.Week)=#" & LoopDate & "#))"
Set rec = db.OpenRecordset(mySQL)
rec.Edit
rec!Remainder = HiresNeeded
rec.Update
rec.Close

LoopDate = LoopDate + 7

Loop Until LoopDate = EndDate + 7

frm.listresults.RowSource = ("SELECT tblResults.Week, tblResults.[Hires Needed], tblResults.[Indianapolis Cap], tblResults.[Saint John Cap], tblResults.[Mexico Cap], tblResults.[Manila Cap], tblResults.[Cork Cap], tblResults.[Indianapolis Hire], tblResults.[Saint John Hire], tblResults.[Mexico Hire], tblResults.[Manila Hire], tblResults.[Cork Hire], tblResults.Remainder FROM tblResults")
frm.listresults.Requery


MsgBox "Finished running!", vbInformation



End Sub

 

[r10701]

Anyone have any thoughts on this? Just hoping to get steered in the right direction.

Thanks

 

[jpstroud]

My first thought on reading your objective, was that an easier way would be to filter the data, ie.. [Priority] > 0.

Let me know if that doesn't accomplish what you're seeking.

Unfortunately, I don't have time at the moment to step through all the code you have there to see what you're accomplishing (I hate giving half answers, but in this case I'm hoping it's enough).

If this hasn't been fully answered later today when I have more time, I'll sort through the code itself and help steer you clear.

 

[r10701]

I couldn't get that to work. Thanks for your help.

 

[jpstroud]

I'll take a peek at this later on today or tomorrow then (unless someone else gets to it). I'm pretty swamped here at the moment or I'd be diving into it.

Questions, was the filter failing outright (nothing filtered?) or was it filtering the wrong data?

 

[r10701]

No problem. I appreciate the help. The filter was failing. When it got to the loop at the bottom I was getting an error.

As it is it works but what I need to be able to do is change a priority for a site to 0 and have the code just skip over that site in the loop. telling it that it must be > 0 didn't work.

Thanks again

 

[jpstroud]

I'll have to look through the code then. My initial thought was based on whether the looping was JUST dropping out the 0 priorities or not (like I said, I hadn't read through the code more than just a cursory glance).

If your intent with the loop is to just remove the 0 priorities, then you don't need the loop, and instead put the filter in the form itself, with FilterOn set to True.

What about trying an IF right before the first bit of code in your statement?

if nz(priority,0) > 0 then
<code>

The NZ will make sure that null values are converted to 0 (and therefor equate to false).

 

[r10701]

My intent with the loop is to go through the priorities starting at 1 and assign hires based on availability and priority. What I need it to do is when the priority for a site is zero the code just goes to the next priority.

I tried the If statement earlier and I get an error at the end of the loop. I just tried the nz and I also get an error. Thanks for your help

 

[r10701]

I still can't seem to get this to work. I have tried many different things and I continue to get a loop error.

Thanks

 

[jpstroud]

Last night I started doing a mockup of your problem. Expect an answer later today

 

[r10701]

Thanks

 

[jpstroud]

A seemingly glaring problem has arisen as I look at the mock-up.

You have a field that defines the hires and caps for each site, within a table. Wouldn't this data be better served as a field (hires & cap) under the tblSiteData? Instead of having to create new fields whenever you add new sites, your information is all kept with the Site Info itself. Search this site for Normalization which describes the technique I'm talking about.

Sheet1_x65 follows the same pattern. Have a Site ID instead of a field for each site.

If this isn't possible, then I'll look at the code, but the changes mentioned above are highly suggested and will make for a cleaner, easier to use database and would be better fixed now than after the code is working.

 

[r10701]

I took a look at those. The two tables you are talking baout are linked to excel spreedsheets where the user will update information on a daily basis. I can't really change the layout now. Those spreedsheets were in use before I made this database.

Thanks,