CODEWITHSUNDEEP
ms-accessvba
user1437797
user1437797

Reputation: 1

ACCESS VBA Comparing a string against a string in another table

here's my problem. I have 2 tables tvehicle and tPMCL the tVehicle table has a list of our vehicles, and the tPMCL holds when preventitive maint. is done.

tvhhicle.VehicleTagnumber holds the actual plate number, and tPMCL.Tag holds only a Index of a look up of that number from when it was entered, I wish it had the tag number so when i do loops through my data comparing it would be able to match up, as it is:

it's comparing something along the lines of "XPE 269" to 1 and that's not working so well.

Any ideas? the answer may not be a VBA answer it may be a diferent way to do the lookup in the first place. But I just can't find another way to do the lookup and actually store the plate number and not an index of it.

Upvotes: 0

Views: 1095

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

It appears that what you think of as Index is actually a foreign key. This is a good thing. This means that if VehicalTagNumber where to change (e.g. a bad input) the referring table would not need to be updated.

If you need to loop through the tPMCL and you need the corresponding Tag Number you can do one of two things.

You could use Dlookup to get it on each loop. e.g. 

Dim strTag As String
strTag = DLookup("[VehicleTagnumber]", "tvhhicle","[Id] = 1")

However this will be slow for large numbers of records.

Instead just base your record set on a SQL statement that joins the two table instead of opening a table directly. 

Dim dbVehicle As Object 
Dim rstVehicle As Object 
Dim fldEnumerator As Object 
Dim fldColumns As Object 
Dim strSQL as String

Set dbVehicle = CurrentDb 
Set rstVehicle = dbVehicle.OpenRecordset("tVehicle") 
Set fldColumns = rstVehicle.Fields 

strSQL = "SELECT * FROM tMPCL m INNER JOIN tVehicle v ON m.Tag = v.ID" 

Set rsttPMCL = dbVehicle.OpenRecordset(strSQL)

Upvotes: 1

Related Questions