ACCESS VBA Comparing a string against a string in another table

Question

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.

Where is the index coming from? How do you get the plate from the index? — Fionnuala
– Fionnuala, Commented Jun 5, 2012 at 16:12
SELECT [tVehicle].[ID], tVehicle.[VehicleTagnumber] FROM tVehicle ORDER BY [VehicleTagnumber]; — user1437797
– user1437797, Commented Jun 5, 2012 at 16:18
Seems like you could change the bound column of your combo box to tVehicle.[VehicleTagnumber] and it would then be storing the tag number directly. You could also add the tag number as the primary key (as I don't see why you'd have two vehicles with the same plate) and re-set your referential integrities to correspond with the new key. — StuckAtWork
– StuckAtWork, Commented Jun 5, 2012 at 16:22

Conrad Frix · Accepted Answer · 2012-06-05 17:27:52Z

1

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)

edited Jun 5, 2012 at 17:27

answered Jun 5, 2012 at 16:39

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user1437797 Over a year ago

You completely lost me here. my skils with VBA are meeger at best, and i am unable to follow the logic of what you posted. I really don't care abvout the sizre if my database. since we'll have at most 5 or 6 vehicles to at any one time. over the life of this program my database might reach 10 of 15 MB even with duplicating the data in the 2 tables.

user1437797 Over a year ago

Dim dbVehicle As Object Dim rstVehicle As Object Dim fldEnumerator As Object Dim fldColumns As Object Set dbVehicle = CurrentDb Set rstVehicle = dbVehicle.OpenRecordset("tVehicle") Set fldColumns = rstVehicle.Fields Set rsttPMCL = dbVehicle.OpenRecordset("tPMCL")

user1437797 Over a year ago

i can't even get my code to display corectly. and the box isn't large enough to get the small amount of code needed anyway.

user1437797 Over a year ago

I think i'm almost there with the help @StuckAtWork was giving.

Conrad Frix Over a year ago

I've updated my answer to include your code in the sample that uses SQL.

|

Collectives™ on Stack Overflow

ACCESS VBA Comparing a string against a string in another table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related