Share via

help with VBA in access recordset

Anonymous
2021-11-27T17:05:33+00:00

Hi

Anyone know what is wrong with below code, I am trying to do like a vlookup in excel where it compares two lists, if it match's the name in each list I want my field in my recordset to have Y as answer - if name not in list then n in my recordset field.

My code happily puts y in first record but updates rest to n, so almost like it's not looking at the if section when it moves next, can anyone advise what is wrong with the syntax?

Thanks

Private Sub matchup_Click()

Dim db As Database

Dim rst As Recordset

Dim strsql As String

Dim compname As String

Dim fullname As String

Set db = CurrentDb

strsql = "SELECT upload.*FROM upload;"

Set rst = db.OpenRecordset(strsql)

rst.MoveFirst

Do Until rst.EOF

If Me.compname = rst!fullname Then 

rst.Edit 

rst!perm = "y" 

rst.Update 

Else 

rst.Edit 

rst!perm = "n" 

rst.Update 

   End If 

    rst.MoveNext

Loop

rst.Close

Set rst = Nothing

Set db = Nothing

End Sub

Microsoft 365 and Office | Access | For education | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2021-11-28T14:57:56+00:00

.................where it compares two lists, if it matches the name in each list I want my field in my recordset to have Y as answer - if name not in list then N

If by two 'lists' you mean two tables then you can easily do that for the complete set with a couple of simple UPDATE queries, e.g.

UPDATE TargetTable

SET TargetColumn= TRUE

WHERE EXISTS

    (SELECT *

     FROM SourceTable

     WHERE SourceTable.ColumnX = TargetTable.ColumnY);

then:

UPDATE TargetTable

SET TargetColumn= FALSE

WHERE NOT EXISTS

    (SELECT *

     FROM SourceTable

     WHERE SourceTable.ColumnX = TargetTable.ColumnY);

where TargetColumn is a Boolean (Yes/No) datatype and ColumnX and ColumnY are the two columns being compared.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

George Hepworth 22,855 Reputation points Volunteer Moderator
2021-11-27T18:24:46+00:00

Many years ago, when I first learned how to create and use recordsets in VBA, I started doing everything I could with recordsets. "See! I can do it!"

Later, after the new wore off, I realized that it's often a whole lot more effective to use a straightforward, plain vanilla, update query to do many of those jobs I'd crafted bulky Recordset code to do.

Then I also realized there is no glory in doing something the hard way (row by row in a recordset) when you can accomplish the same objective the easy way (a set operation in SQL). Chastened I stopped looking for ways to use recordsets and started picking the right approach for each task as it presented itself. I still use recordsets when that appears to be the more appropriate method, though.

You could probably do the same, I think, as Duane suggested.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
2021-11-27T17:36:57+00:00

> strsql = "SELECT upload.*FROM upload;"

There should be a space before FROM.

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Duane Hookom 26,825 Reputation points Volunteer Moderator
2021-11-27T17:32:55+00:00

I don't see anything in your code that suggests a "name in each list".

I expect you could do this more efficiently with by executing an SQL statement:

Dim strSQL as String

strSQL = "UPDATE upload SET perm = 'y' WHERE FullName = '" & Me.compname & "'"

Currentdb.Execute strSQL, dbFailOnError

I would also Dim the objects as DAO:

Dim db As DAO.Database

Dim rst As DAO.Recordset

Was this answer helpful?

1 person found this answer helpful.
0 comments

6 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2021-11-27T17:44:32+00:00

    You say you want to compare two lists. In your code I see that you compare each fullname from "upload" (first list) to the same value (me.compare). So what is the second list?

    Was this answer helpful?

    0 comments