After Update Validation (1 Viewer)

[mdvr613]

Hello all,

I'm currently building a simple incoming package tracker.

My current issue is creating a "after update" validation rule off the Tracking number field that would determine the length needed based on the Courier choosen (Macro).

I have a incoming_tbl with a Courier dropdown as it relates to courier_tbl which is formated as follows:

CID Courier
1 FedEx
2 UPS
3 DHL


The Tracking Number field is within the incoming_tbl that also captures other collection data (Time, Date, Sign by, etc)

What I am looking to accomplish is when Courier is selected, depending on the type of courier the tracking field ensures the correct amount of characters are entered

i.e
FedEx 12
UPS 18
DHL 10

I've looked at various threads but didn't come across anything that changes the value of the field based on the dropdown selection; I've tried SetField and Len with no positive outcome as of yet; but I might be using it out of context.

Thanks for any quidance and/or assistance, greatly appreciated.

 

[Galaxiom]

Add a TrackingNumberLength field to the Courier table.

Use a DLookup in the validation code to compare the length of the TrackingNumber field with the value stored in the table.

 

[mdvr613]

Add a TrackingNumberLength field to the Courier table.

Use a DLookup in the validation code to compare the length of the TrackingNumber field with the value stored in the table.

Thanks for the guidance Galaxiom,

I was trying to see if I could create the validaiton rule within the table iteself as such will be linked to a Sharepoint Site that will have form built in InfoPath....unfortunetly there is never an easy way out and a second or third step is always needed.

For reference (for those searching these forums with a similiar question)Access validation rules are limited to those built within Access when creating validation rules in a table, it will not let you use the ones you created.

I did take your advise on adding an additional column with the length and will either use the dlookup function within the form or might use this as code as an after update (just sharing information)

Public Function TrackingLength(CourierName As String)
Dim RS As Recordset
Set RS = CurrentDb.OpenRecordset("Courier")
RS.MoveLast
RS.MoveFirst
Do Until RS.EOF
If RS("courier") = CourierName Then Exit Do
RS.MoveNext
Loop

TrackingLength = RS("Tracking Length")
End Function

Thanks again Galaxiom and good day
Item can be noted as Resolved.