Using RecordSet to update table? (1 Viewer)

[Talismanic]

I am still learning to use recordsets out side of ASP and still having problems with it. I want to update the "Jobs" table with (for now) static data. Here is what I have so far:

Dim rsCOD As DAO.Recordset
Dim strJobName, strJobNumb, strLocation As String

strJobName = "Talismanic"
strJobNumb = "91269"
strLocation = "Access World Forum"

Set rsCOD = CurrentDb.OpenRecordset("Jobs")

rsCOD.addnew
rsCOD([JobName]) = strJobName
rsCOD([JobNumber]) = strJobNumb
rsCOD([Location]) = strLocation
rsCOD.Update

Set rsCOD = Nothing

Which gives me this error

I am guessing there is a problem with the way I opened the recordset or the way I refferenced the fields inside it. Any ideas?

 

[Rich]

Try something like
rsCOD.AddNew
rsCOD!JobName = strJobName
etc
rsCOD.Update
HTH

 

[Talismanic]

Rich,

I modified it like you said and now I am getting a Compile Error - Method or Data Member not Found and it highlights this .JobName

My table and field names are correct so I am pretty sure its not that. I am opening the recordset correctly? This is how the code looks with the modification.

Dim rsCOD As Recordset
Dim strJobName, strJobNumb, strLocation As String

strJobName = "Buck Hicks"
strJobNumb = "91269"
strLocation = "Three Rivers"

Set rsCOD = CurrentDb.OpenRecordset("Jobs")

rsCOD.addnew
rsCOD.JobName = strJobName
rsCOD.jobnumb = strJobNumb
rsCOD.Location = strLocation
rsCOD.Update

Set rsCOD = Nothing

 

[Rich]

Not a dot but a bang ! I'm certain it should be
rsCOD!JobName = strJobName
rsCOD!jobnumb = strJobNumb
rsCOD!Location = strLocation

HTH

 

[Talismanic]

Thanks Rich, that did it.

 

[R. Hicks]

Talismanic, this is not related to your problem .... but I just wanted to point this out.

In your declaration of your variables you have the following line:

Dim strJobName, strJobNumb, strLocation As String

If you are assuming that these are all being declared as "String Datatypes" ..... you are mistaken. Only "strLocation" is declared as a String.

Even if they are on the same line, as you have it, you must define the datatype on each variable. Omitting them, as you did in the first two will cause Access to assign the first two as "Variants".

So to have all three as String Variables it should be:

Dim strJobName As String, strJobNumb As String, strLocation As String

Or

Dim strJobName As String
Dim strJobNumb As String
Dim strLocation As String

Just wanted to point this out this in case you didn't know.

RDH

[This message has been edited by R. Hicks (edited 08-30-2001).]

 

[BukHix]

Thanks R.Hicks, I have been working with ASP 3.O where everything is a varient so you don't declare specific types. I was assuming that all those would be declared as strings. I think I have seen developers do something like this:

Dim strOne as String, strTwo as String

But just to be safe I dimmed my variables seperately and line by line. If anyone is interested or finds this thread in a search, here is how the final code ended up.

Dim intQuestion As Integer
Dim intLocation As Integer
Dim rsCOD As Recordset
Dim strJobName As String
Dim strJobNumb As String
Dim strTitle As String

intQuestion = MsgBox("Does this job need to be filed?" & vbCrLf & _
vbCrLf & "Select YES to File or NO to save now.", vbYesNo, "Job Status")

If intQuestion = vbYes Then

strJobName = JobName
strJobNumb = JobNumber

Select Case CodeLoc
Case "GR WallsCeilings"
intLocation = 1
strTitle = "GR WallsCeilings"
Case "GR Flooring"
intLocation = 3
strTitle = "GR Flooring"
Case "Kalamazoo All"
intLocation = 5
strTitle = "Kalamazoo All"
Case "Traverse City All"
intLocation = 7
strTitle = "Traverse City All"
End Select

Set rsCOD = CurrentDb.OpenRecordset("Jobs")
rsCOD.addnew
rsCOD!JobName = strJobName
rsCOD!jobnumb = strJobNumb
rsCOD!Location = intLocation
rsCOD!Title = strTitle
rsCOD.Update
Set rsCOD = Nothing

Else

Exit Sub

End If

[This message has been edited by BukHix (edited 09-25-2001).]