Get value that is between value's in two fields

[wwiSports]

I have field Zip and Field Zone in my form. I also have a Table Zones.

Table Zones has the following:
ZoneID, ZoneZipStart, ZoneZipEnd, Zone

I would like the form once I leave the Zip field to fill in the Zone field with the Zone that zip code;

For instance if the zip code entered is 20164

ZoneZipStart = 20000
ZoneZipEnd = 29999
Zone = 1

I dont know what code I would use on the Zip_LostFocus() for it to look in the Zones table, find the ZoneZipStart and ZoneZipEnd it is between, and enter the Zone into the Zone field on my form.

Am I making sense? Cause I think i'm confusing myself now

Beth

 

[FoxProProgrammer]

You can use the DLookup function to look up the zone. Give this a try in the Exit event of the Zip text box:

Zone.Value = DLookup(&quot;Zone&quot;, &quot;Zones&quot;, &quot;Zip < ZoneZipEnd And Zip > ZoneZipStart&quot;

dz
dzaccess@yahoo.com

 

[wwiSports]

This works, as far as the Dlookup is concerned. It is pulling the correct zones, but it's not saving the zones. I have the following code to save everything:
Private Sub SaveAthInfo()
Dim strSQL As String
AthChange = False
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
strSQL = &quot;select * from athlete &quot;
strSQL = strSQL & &quot; where AthleteID = '&quot; & AthleteID & &quot;'&quot;
Set rst = dbs.OpenRecordset(strSQL)
rst.Edit
rst!Address1 = Address1
rst!Address2 = Address2
rst!Biography = Biography
rst!City = City
If IsDate(DSRcvdDate) Then
rst!DSRcvdDate = DSRcvdDate
End If
rst!PrimarySport = PrimarySport
rst!PositionEvent = PrimaryEvent
rst!Email = Email
rst!FirstName = FirstName
rst!MidName = MiddleName
rst!LastName = LastName
rst!Gender = Gender
rst!GradYear = GradYear
rst!HeightFt = HeightFt
rst!HeightIn = HeightIn
rst!Weight = Weight
rst!Phone = Phone
rst!Pic = Pic
rst!PicID = PicID
rst!SchoolName = SchoolName
rst!SchoolState = SchoolState
rst!State = State
rst!ZIP = ZIP
rst!Zone = Zone
rst!RespByDt = RespByDt
rst.Update

End Sub

And when I go to a previous record I have the following code:

Private Sub cmdPrevAthlete_Click()
On Error GoTo Err_Click
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database

OldBio = &quot;&quot;
OldQty = 0
OldStockNum = 0
If AthChange Then SaveAthInfo
If InvChange Then SaveInvInfo
Set dbs = CurrentDb
strSQL = &quot;select athleteid from athlete&quot;
Set rst = dbs.OpenRecordset(strSQL)
rst.FindFirst &quot;AthleteID = '&quot; & AthleteID & &quot;'&quot;
rst.MovePrevious
If rst.BOF Then
MsgBox &quot;You are at the first record&quot;, vbInformation, &quot;First Record&quot;
GoTo EndOfSub
End If

PopulateFields rst!AthleteID, 0, &quot;stocknum1&quot;
Me.Caption = &quot;Existing Athlete&quot;

EndOfSub:
Set rst = Nothing
Set dbs = Nothing
Exit Sub

Err_Click:
MsgBox Err.Description
Resume EndOfSub

End Sub

And this is the Populate fields code:

Private Sub PopulateFields(AthID As String, InvNum As String, FocFld As String)

ReDim aStockCost(1 To MaxItms) As Currency
ReDim aStockSandH(1 To MaxItms) As Currency
ReDim aInvLineNum(1 To MaxItms) As Integer
ReDim aQty(1 To MaxItms) As Integer
ReDim aStockDesc(1 To MaxItms) As String
ReDim aStockNum(1 To MaxItms) As Integer
ReDim aShipDt(1 To MaxItms) As Date
Dim strSQL As String
Dim dbs As Database
Dim MoreItems As Boolean



If AthChange Then SaveAthInfo
If InvChange Then SaveInvInfo

Set dbs = CurrentDb
If InvNum = &quot;0&quot; Or InvNum = &quot;-1&quot; Then
strSQL = &quot;select * from QOrderQuery where athleteid = '&quot; & AthID & &quot;'&quot;
Else
strSQL = &quot;select * from QOrderQuery where athleteid = '&quot; & AthID & &quot;'&quot;
strSQL = strSQL & &quot; and invoicenum = '&quot; & InvNum & &quot;'&quot;
End If
strSQL = strSQL & &quot; order by athleteid, invoicenum, invlinenum&quot;
' Debug.Print strSQL
Set AthRst = dbs.OpenRecordset(strSQL)
AthleteID = AthRst!AthleteID
FirstName = AthRst!FirstName
MiddleName = AthRst!MidName
Address1 = AthRst!Address1
Address2 = AthRst!Address2
City = AthRst!City
State = AthRst!State
ZIP = AthRst!ZIP
Email = AthRst!Email
PrimarySport = AthRst!PrimarySport
PrimaryEvent = AthRst!PositionEvent
ConfLtr = AthRst!ConfLtrDt
Biography = AthRst!Biography
Weight = AthRst!Weight
DSRcvdDate = AthRst!DSRcvdDate
'it is critical that the next several lines remain in this order *
InvoiceDate = AthRst!InvoiceDate '*
If InvoiceDate = &quot;&quot; Or IsNull(InvoiceDate) Then '*
If FrmMode = &quot;NEW&quot; Then '*
InvoiceDate = DSRcvdDate '*
Else '*
InvoiceDate = GetSetting(&quot;WWIS&quot;, &quot;Defaults&quot;, &quot;DSDt&quot;, &quot;&quot
End If '*
End If '*
'end**************************************************************
InvOnHold = AthRst!InvOnHold
Gender = AthRst!Gender
GradYear = AthRst!GradYear
HeightFt = AthRst!HeightFt
HeightIn = AthRst!HeightIn
InvoiceNum = AthRst!InvoiceNum
If IsNull(InvoiceNum) Then
InvoiceNum = AthleteID & &quot;001&quot;
strSQL = &quot;INSERT INTO invoice &quot;
strSQL = strSQL & &quot;(invoicenum, athleteid) VALUES ('&quot;
strSQL = strSQL & InvoiceNum & &quot;', '&quot; & AthID & &quot;');&quot;
DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True
End If
LastName = AthRst!LastName
Phone = AthRst!Phone
Pic = AthRst!Pic
PicID = AthRst!PicID
RespByDt = AthRst!RespByDt
CheckName = AthRst!CheckName
CardHolder = AthRst!CardHolder
CardNumber = AthRst!CardNumber
CardExpDate = AthRst!CardExpDate
PmtAmt = AthRst!PmtAmt
PmtType = AthRst!PmtType
SchoolName = AthRst!SchoolName
SchoolState = AthRst!SchoolState
ShipTo1 = AthRst!ShipTo1
ShipTo2 = AthRst!ShipTo2
FirstName_LostFocus
ShipAddr1 = AthRst!ShipAddr1
CheckShip ShipAddr1, Address1
ShipAddr2 = AthRst!ShipAddr2
CheckShip ShipAddr2, Address2
ShipCity = AthRst!ShipCity
CheckShip ShipCity, City
ShipState = AthRst!ShipState
CheckShip ShipState, State
ShipZIP = AthRst!ShipZIP
CheckShip ShipZIP, ZIP
Notes1 = AthRst!Notes
If IsNull(Me.Notes1) Then
Me.lblSeeNotes.Visible = False
Else
Me.lblSeeNotes.Visible = True
End If

ItmsOnOrd = 0
NextItm = 0
SubTotal = 0
FirstShown = 1
MoreItems = True
Do While MoreItems
ItmsOnOrd = ItmsOnOrd + 1
NextItm = NextItm + 1
If IsNull(AthRst!InvLineNum) Then
aInvLineNum(NextItm) = 0
ItmsOnOrd = ItmsOnOrd - 1
Else
aInvLineNum(NextItm) = AthRst!InvLineNum
If aInvLineNum(NextItm) > NextItm Then
NextItm = aInvLineNum(NextItm)
End If
If aInvLineNum(NextItm) = CurrentLine Then
FirstShown = ItmsOnOrd - 1
End If
End If
aStockNum(NextItm) = Nz(AthRst!StockNum, 0)
aStockDesc(NextItm) = Nz(AthRst!StockDesc, &quot;&quot
aQty(NextItm) = Nz(AthRst!Qty, 0)
aStockCost(NextItm) = Nz(AthRst!StockCost, 0)
aStockSandH(NextItm) = Nz(AthRst!StockSandH, 0)
aShipDt(NextItm) = Nz(AthRst!ItemShipDate, 0)
AthRst.MoveNext
If AthRst.EOF Then
MoreItems = False
ElseIf InvoiceNum <> AthRst!InvoiceNum Then
MoreItems = False
End If
Loop
'NextItm = NextItm + 1
If FirstShown < 1 Then
FirstShown = 1
StockNum1.SetFocus
End If
DisplayItems (FirstShown)

If ItmsOnOrd > 4 Then 'Enable the Down button if there are more than 4 items on this order
cmdNextItm.Enabled = True
cmdNextItm.Visible = True
Else
cmdNextItm.Enabled = False
cmdNextItm.Visible = False
End If

If FirstShown = 1 Then 'Disable the Up button if first item shown is first item on order
cmdPrevItm.Enabled = False
cmdPrevItm.Visible = False
Else
cmdPrevItm.Enabled = True
cmdPrevItm.Visible = True
End If


CalcTotal

CurrentLine = 0
Me(FocFld).SetFocus

End Sub 'PopulateFields

I thought the error was in the above populate codes, and that I had to put Zone = AthRst!Zone. But it give's me an error every time I do.

&quot;Item not found in this collection&quot;

HELP!!!!

 

[FoxProProgrammer]

Hi Beth,

You would get this error if the field named &quot;Zone&quot; doesn't exist in the record set named &quot;AthRst&quot;. The name of the recordset in the procedure where you save the fields is &quot;rst&quot;. The name of the recordset in the procedure where you populate the fields is &quot;AthRst&quot;. If you want to save the zone, shouldn't you include something like...

rst!zone in the procedure named &quot;SaveAthInfo&quot;?

Are you able to save the other fields in SaveAthInfo ok? dz
dzaccess@yahoo.com