CODEWITHSUNDEEP
vbams-accessms-access-2007
Panagiotis Gianniotis
Panagiotis Gianniotis

Reputation: 17

Search through an Access Table Column with VBA and return the ID of the searched entry

I have an Access Database (not made by me) that contains all the projects of my company. Right now I am trying to automate the way we make new entries into that Database by using VBA. The data we input are taken from excel files and VBA is an easy way to connect those two.

However, I am encountering a problem into pasting the values from excel to an access form because some of the comboboxes in that form are linked to table queries and for some reason using combobox.value = "excel value" doesn/t work. I realised that that in order to make it work I have to find the ID of the value I want to transfer from excel.

Long story short: Is there a way to look for a specific value in an Access Table Column with VBA and return the ID Number (Primary Key column) of that value ? FYI I have limited knowledge of Access

Upvotes: 0

Views: 2366

Answers (1)

Andre
Andre

Reputation: 27634

The DLookup() function does this.

strProjectName = "excel value"
myID = DLookup("ID", "myTable", "ProjectName = '" & strProjectName & "'")

But this will only work if the criteria values are unique (no duplicates).

Usually you wrap DLookup with Nz() to handle NULL results, if the value is not found.

Upvotes: 1

Related Questions