Updating records and getting Runtime Error 3075

[reidi_lexi]

Hello there!
For the access program I am going to do, there is already an existing data. I had imported it from Excel. Now, I have created a URN field from the look up table and wanted to update it to the main table which comprises the existing data. I've used the following code but I keep on getting the Runtime Error 3075 message. Here is my code:

Private Sub Command0_Click()
Dim md As Recordset
Dim phr As Recordset
Dim entry As Recordset
Dim phrname As String
Dim mdname As String

Set md = CurrentDb.OpenRecordset("tblMDinfo")
Set phr = CurrentDb.OpenRecordset("tblPHRinfo")
Set entry = CurrentDb.OpenRecordset("tblEntryTrack")
phrname = phr("fldPHR")
mdname = md("fldMDName")

Do While entry.EOF = False
CurrentDb.Execute "update tblEntryTrack set [tblEntryTrack].[fldPHRID] = [tblPHRinfo].[fldPHRID] where [tblEntryTrack].[fldPHR] = " & phrname & ";"
CurrentDb.Execute "update tblEntryTrack set [tblEntryTrack].[fldMDID] = [tblMDinfo].[fldMDID] where [tblEntryTrack].[fldMDName] = " & mdname & ";"
Loop
MsgBox "done!"
End Sub

Help anyone?

 

[Galaxiom]

phrname is a string so needs to be in quotes

 ..... = [COLOR=red]'[/COLOR]" & phrname & "[COLOR=red]'[/COLOR];"

 

[reidi_lexi]

Thanks for your reply...I've done that but now I'm getting Runtime Error 3061...it said that too few parameters. expected 1.

 

[Galaxiom]

This line is incorrect:
phrname = phr("fldPHR")

Should be:
phrname = phr!fldPHR

Likewise the line under it.

 

[reidi_lexi]

Still getting the same error message...

 

[Galaxiom]

Actually I think the original reference to the recordset field is valid too. Was late last night and I was clutching at straws.

The real problem is your sql is not valid because you have [tblPHRinfo] coming from nowhere. Needs a FROM clause.

Build it in the query designer first, then copy the sql over to VBA and edit the variable part.

 

[reidi_lexi]

Hi Galaxiom...I quit on doing it on code and have sorted it out using query instead... Thanks anyway!