Noob - Update record in table

[systematic]

Hi all,

First of all....my first access database and I'm still a VB newbie...so my apologies if this is a stupid question. I tried searching the web and this forum but could'nt find what I was looking for.

Basically, I am developing an authentication script for a training tutorial. The user either logs in with there existing info, or prior creates a new record. When they login they also select a team (eg Team 1, 2, 3, 4, admin and so on). This is selected from a combo box - the teams are on their own table.

I have another table storing the user info - name, password, the users team...and so on. When they log in and the team selected does not match the team in 'tblusers' - I want access to change the record to reflect the new team.

Can anyone help me with the code to make this happen? So far I have -

If Me.defineteam.Value <> DLookup("Team", "tblusers") Then
MsgBox "You have changed team. This will now be updated", vbOKOnly, "Required Data"
GoTo verifypassword:
End If

This authenticates the team....I just don't know how to replace the one in the record with the value in the combobox.

Thanks for your help.....I'm losing hair!

cheers

Sd

 

[ScottGem]

Dim strSQL As String

If Me.defineteam <> DLookup("Team", "tblusers","[userID] =" & Me!userId) Then
MsgBox "You have changed team. This will now be updated", vbOKOnly, "Required Data"
strSQL = "UPDATE table SET Team = " & Me.defineteam
strSQL = strSQL & " WHERE UserID = " & Me!userID & ";"
CurrentDB.Execute strSQL
GoTo verifypassword:
End If

I'm not sure how your DLookup would work since it would only pull the team from the first record in tblUsers. That's why I added the criteria clause. You then build an SQL Update statement to update that user's record. Note you need to use the correct field names and data types, but the logic is correct.

 

[Pat Hartman]

As long as the form is bound, you can use the OldValue property. It shows the value prior to the change:
If Me.defineteam <> Me.defineteam.OldValue Then

 

[systematic]

Hi again,

Thanks for your help! I have tried to get the code working with no success. At first run I received a syntax error (with UPDATE statement).

My vb script looks like this -

If Me.defineteam <> DLookup("Team", "tblEmployees", "[lngEmpID] =" & Me!cboEmployee.Value) Then
MsgBox "Thank you. Your team has been updated", vbOKOnly, "Team Updated"
strSQL = "UPDATE table SET Team = " & Me.defineteam
strSQL = strSQL & " WHERE lngEmpID = " & Me!cboEmployee.Value & ";"
CurrentDb.Execute strSQL
Call VerifyPassword

I have tried all sorts - all resulting in further errors. Have also tried playing with the recordset command to no avail. I did'nt mention that I am programming the VB in access.

Once again, any help to get this right would be greatly appreciated.

 

[ScottGem]

It might help if you detailed the errors.

First, there is no reason to use .Value for a control. That's the default, I never use it. Second, you need to make sure your data types are correct. What data type is Team? What data type is returned by DefineTeam? If these are text fields you need to concatenate in single quotes (i.e. Team ='" & Me.Defineteam & "'")

 

[Pat Hartman]

Did you even look at the OldValue property?

 

[systematic]

Hi guys,

Just wanted to thank you both again for your help. My apologies for the delay in getting back to you - I have been offline for some time.

Scott - you were correct in stating that the parenthesis were missing from the statement. Just as a note on that - using the single parenthesis outside of the double did not work for me - I am unsure why (but probably something I missed!). I ended up using

=" & Chr(34) & Me.defineteam & Chr(34)

which worked a treat.

Pat - I did'nt get a chance to play around with the OldValue tag - but am currently tinkering with an undo function on one of my forms so will be using it there! Thanks mate

Thanks again both for your assistance. I really appreciate it.

 

[ScottGem]

concatenating CHR(34) is the equivalent of concatenting in a single quote.

="'" & Me.defineteam & "'"

Would have also worked