Look up and edit a field using VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can't seem to figure out how to do this. I need to be able to look at
existing data in a table field, and edit data in a table field, using VBA
code. I can't figure out how to reference table fields in my code. I keep
getting "Object Required" errors and the like. How do you reference a field
from a table, and then edit and/or look up that field via code? The main
reason I need to know how to do this is that I'm needing to look at a table
to verify that a password entered is correct.
 
Here are 2 approaches. While they are aircode they should point you toward a
productive outcome:

Dim db as DAO.Database
Dim rs as DAO.Recordset

Set db = CurrentDB
Set rs = db.OpenRecordset ("SomeTable")

Do Until rs.EOF
If rs!FieldName = "some value" Then
rs.Edit
rs.Fields("FieldName") = "something else"
rs.Update
End If

rs.MoveNext
Loop

rs.Close

Set rs = Nothing
Set db = Nothing

Once you have the recordset open there are various syntax you can use to
reference a field: rs("FieldName") rs!FieldName, rs![Field
Name]rs.Fields(1), etc. Pick your poison.


Another approach would be to write a SQL string in the form of an Update
query and run that:
strSQL = "UPDATE SomeTable SET SomeField = 'Something Else' WHERE
SomeField = 'SomeValue'"
CurrentDB.Execute strSQL, dbFailOnError

In addition, if you just want to look up data (not edit it) you might find
Dlookup() useful:
strContentsOfSomeOtherField =
Dlookup("[SomeOtherField]","SomeTable","[SomeField] = 'SomeValue'")


HTH,
 
It might be easier if you were to place your code here for us to examine.
There are several ways to reference table fields depending on your need.
 
I am going to post a paraphrased version of my code (a lot of it is
if-then-else loops, so I'm just going to post the first one and erase the
rest), maybe someone can make sense of it and help me with my issues.

**************
Function OpenUserForms()
Dim strUserName As String
Dim strPassword As String
Dim FileType As String
Dim RepName As String
Dim ImportTableName As String
Dim FileToImport As String
Dim BDCC
Dim RepFile
strUserName = Forms![Login Screen]![Username]
strPassword = Forms![Login Screen]![Password]
***The following lines are a previous attempt at making this code work,
which didn't do what I wanted, but the basic structure and idea is the same,
so I'm leaving them in so you can see what I was getting at.***
If strUserName = "CG" Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "PasswordVerify"
If IsPassCorrect!PassCorrect = "True" Then
DoCmd.OpenForm ("Comisha")
Else
MsgBox "The password is incorrect, please try again."
End If
***Continuing the code...***
....
If strUserName = "Admin" Then
DoCmd.OpenForm ("Admin")
For BDCC = 1 To 2
If BDCC = 1 Then
FileType = "BD"
Else
FileType = "CC"
End If
For RepFile = 1 To 7
If RepFile = 1 Then
RepName = "CG"
Else: End If
....
ImportTableName = FileType & "" & RepName
FileToImport = "c:\" & FileType & "" & RepName & ".xls"
If Len(Dir(FileToImport)) > 0 Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
ImportTableName, FileToImport, True
Else: End If
Next RepFile
Next BDCC
End If
End Function
**************

The idea behind the code is, the login screen has 2 fields, Username and
Password. If Username is a customer service rep, then the code opens their
main menu form, provided the password matches. If Username is "admin" and the
password matches, then the code imports the files from each customer service
rep (they are emailed to the supervisor at the end of the day for processing
and approval) automatically and runs the processes necessary on the imported
data. The problem is, I want to give the customer service reps the option to
change their own passwords, and in order to do that, I need to store those
values in a table, which I can't access from my code, hence my problem.
 
I used the Dlookup code to grab the passwords from the form and compare them
to what was stored in the tables and it worked great, thanks George. Now all
I need is to be able to edit those passwords, which I can do with an entry
form. Thanks!
 
Back
Top