Using VBA to enter a value into a table

  • Thread starter Thread starter Julie
  • Start date Start date
J

Julie

Hello Everyone,

I would like to enter the number 3 into a field in my table using VBA. So
far I have:

Private Sub Stastdt_Exit(Cancel As Integer)
'to close the job or not to close the job that is the question
Dim intAnswer As Integer
Dim db As Database
Dim rs As Recordset
Dim stLinkCriteriaIF As String
Dim stDocNameIF As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("job", dbOpenSnapshot)

intAnswer = MsgBox("Would you like to close the job?", vbYesNo, "To close or
not to close?")
Select Case intAnswer
Case vbYes
While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
I = "Gotcha"
End If
rs.MoveNext
Wend

If I = "Gotcha" Then
rs("Jobsta") = "3"
End If
End Select
End Sub

When I say yes to closing this job, I get the error:
Cannot update. Database object read only.

Does anyone know how I can get around this?

Thanks and have a Happy Thanksgiving!
Julie
 
A snapshot recordset is not updatable.

Change dbOpenSnapshot to dbOpenDynaset.

Also, you need to insert rs.Edit and rs.Update:
If I = "Gotcha" Then
rs.Edit
rs("Jobsta") = "3"
rs.Update
End If
 
Thank you Ken, we are getting closer I can feel it. Now I am getting the
error "No current record" and the line rs.edit is highlighted. Any ideas?

Ken Snell said:
A snapshot recordset is not updatable.

Change dbOpenSnapshot to dbOpenDynaset.

Also, you need to insert rs.Edit and rs.Update:
If I = "Gotcha" Then
rs.Edit
rs("Jobsta") = "3"
rs.Update
End If


--
Ken Snell
<MS ACCESS MVP>
Julie said:
Hello Everyone,

I would like to enter the number 3 into a field in my table using VBA. So
far I have:

Private Sub Stastdt_Exit(Cancel As Integer)
'to close the job or not to close the job that is the question
Dim intAnswer As Integer
Dim db As Database
Dim rs As Recordset
Dim stLinkCriteriaIF As String
Dim stDocNameIF As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("job", dbOpenSnapshot)

intAnswer = MsgBox("Would you like to close the job?", vbYesNo, "To
Click to expand...
close
or
not to close?")
Select Case intAnswer
Case vbYes
While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
I = "Gotcha"
End If
rs.MoveNext
Wend

If I = "Gotcha" Then
rs("Jobsta") = "3"
End If
End Select
End Sub

When I say yes to closing this job, I get the error:
Cannot update. Database object read only.

Does anyone know how I can get around this?

Thanks and have a Happy Thanksgiving!
Julie
Click to expand...
Click to expand...
 
The code that precedes this is looping through the recordset, all the way to
the EOF. So of course there is no current record when the rs.Edit code runs
(sorry...didn't see that initially).

Which record do you want to edit? Need to know that so that we can set this
last block of code up correctly.
--
Ken Snell
<MS ACCESS MVP>

Julie said:
Thank you Ken, we are getting closer I can feel it. Now I am getting the
error "No current record" and the line rs.edit is highlighted. Any ideas?

Ken Snell said:
A snapshot recordset is not updatable.

Change dbOpenSnapshot to dbOpenDynaset.

Also, you need to insert rs.Edit and rs.Update:
If I = "Gotcha" Then
rs.Edit
rs("Jobsta") = "3"
rs.Update
End If


--
Ken Snell
<MS ACCESS MVP>
Julie said:
Hello Everyone,

I would like to enter the number 3 into a field in my table using VBA. So
far I have:

Private Sub Stastdt_Exit(Cancel As Integer)
'to close the job or not to close the job that is the question
Dim intAnswer As Integer
Dim db As Database
Dim rs As Recordset
Dim stLinkCriteriaIF As String
Dim stDocNameIF As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("job", dbOpenSnapshot)

intAnswer = MsgBox("Would you like to close the job?", vbYesNo, "To
Click to expand...
close
or
not to close?")
Select Case intAnswer
Case vbYes
While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
I = "Gotcha"
End If
rs.MoveNext
Wend

If I = "Gotcha" Then
rs("Jobsta") = "3"
End If
End Select
End Sub

When I say yes to closing this job, I get the error:
Cannot update. Database object read only.

Does anyone know how I can get around this?

Thanks and have a Happy Thanksgiving!
Julie
Click to expand...
Click to expand...
Click to expand...
 
Ken,

I thought that the...Then I = "Gotcha"...acted as a flag and pulled the
record aside?


The record I am updating is:
Set rs = db.OpenRecordset("job", dbOpenSnapshot)
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] = rs("JobSer")

I need the field rs("Jobsta") to be updated to "3"

In other words: I need the code to
1) go to the [job] table
2) find the record where [job]![JobSer] matches the [StaJobSer] of the
record that I am currently viewing (there should only be one record)
3) update the [job]![Jobsta] to "3".

Does this make sense?

Thank you again for your help. I hope that you enjoyed your Thanksgiving.

Julie



Ken Snell said:
The code that precedes this is looping through the recordset, all the way to
the EOF. So of course there is no current record when the rs.Edit code runs
(sorry...didn't see that initially).

Which record do you want to edit? Need to know that so that we can set this
last block of code up correctly.
--
Ken Snell
<MS ACCESS MVP>

Julie said:
Thank you Ken, we are getting closer I can feel it. Now I am getting the
error "No current record" and the line rs.edit is highlighted. Any ideas?

Ken Snell said:
A snapshot recordset is not updatable.

Change dbOpenSnapshot to dbOpenDynaset.

Also, you need to insert rs.Edit and rs.Update:
If I = "Gotcha" Then
rs.Edit
rs("Jobsta") = "3"
rs.Update
End If


--
Ken Snell
<MS ACCESS MVP>
Hello Everyone,

I would like to enter the number 3 into a field in my table using
Click to expand...
VBA.
So
far I have:

Private Sub Stastdt_Exit(Cancel As Integer)
'to close the job or not to close the job that is the question
Dim intAnswer As Integer
Dim db As Database
Dim rs As Recordset
Dim stLinkCriteriaIF As String
Dim stDocNameIF As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("job", dbOpenSnapshot)

intAnswer = MsgBox("Would you like to close the job?", vbYesNo, "To close
or
not to close?")
Select Case intAnswer
Case vbYes
While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
I = "Gotcha"
End If
rs.MoveNext
Wend

If I = "Gotcha" Then
rs("Jobsta") = "3"
End If
End Select
End Sub

When I say yes to closing this job, I get the error:
Cannot update. Database object read only.

Does anyone know how I can get around this?

Thanks and have a Happy Thanksgiving!
Julie
Click to expand...
Click to expand...
Click to expand...
 
This is the code of which I write:

While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
I = "Gotcha"
End If
rs.MoveNext
Wend

This code loops through the entire recordset to EOF. It does not remember
the specific record when the I variable is set, so when your next code wants
to edit the recordset, you can't move back to that record before editing.

My previous answer still applies. Your code that is trying to edit the
recordset rs is doing it at the EOF label, not on a record.
--
Ken Snell
<MS ACCESS MVP>


Julie said:
Ken,

I thought that the...Then I = "Gotcha"...acted as a flag and pulled the
record aside?


The record I am updating is:
Set rs = db.OpenRecordset("job", dbOpenSnapshot)
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] = rs("JobSer")

I need the field rs("Jobsta") to be updated to "3"

In other words: I need the code to
1) go to the [job] table
2) find the record where [job]![JobSer] matches the [StaJobSer] of the
record that I am currently viewing (there should only be one record)
3) update the [job]![Jobsta] to "3".

Does this make sense?

Thank you again for your help. I hope that you enjoyed your Thanksgiving.

Julie



Ken Snell said:
The code that precedes this is looping through the recordset, all the
Click to expand...
way
to
the EOF. So of course there is no current record when the rs.Edit code runs
(sorry...didn't see that initially).

Which record do you want to edit? Need to know that so that we can set this
last block of code up correctly.
--
Ken Snell
<MS ACCESS MVP>

Julie said:
Thank you Ken, we are getting closer I can feel it. Now I am getting the
error "No current record" and the line rs.edit is highlighted. Any ideas?

A snapshot recordset is not updatable.

Change dbOpenSnapshot to dbOpenDynaset.

Also, you need to insert rs.Edit and rs.Update:
If I = "Gotcha" Then
rs.Edit
rs("Jobsta") = "3"
rs.Update
End If


--
Ken Snell
<MS ACCESS MVP>
Hello Everyone,

I would like to enter the number 3 into a field in my table using VBA.
So
far I have:

Private Sub Stastdt_Exit(Cancel As Integer)
'to close the job or not to close the job that is the question
Dim intAnswer As Integer
Dim db As Database
Dim rs As Recordset
Dim stLinkCriteriaIF As String
Dim stDocNameIF As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("job", dbOpenSnapshot)

intAnswer = MsgBox("Would you like to close the job?", vbYesNo, "To
close
or
not to close?")
Select Case intAnswer
Case vbYes
While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
I = "Gotcha"
End If
rs.MoveNext
Wend

If I = "Gotcha" Then
rs("Jobsta") = "3"
End If
End Select
End Sub

When I say yes to closing this job, I get the error:
Cannot update. Database object read only.

Does anyone know how I can get around this?

Thanks and have a Happy Thanksgiving!
Julie
Click to expand...
Click to expand...
Click to expand...
 
Ken,
Thank you for coming back and checking to see if my problem has been solved
after all this time. (Because of the holiday I am getting a little more time
off.) The following code works beautifully, thank you for explaining. I
think I understand, but if you could answer one more question for me. Would
the following code work if I had multiple records to update and not just
one?

Private Sub Stastdt_Exit(Cancel As Integer)
'to close the job or not to close the job that is the question
Dim intAnswer As Integer
Dim db As Database
Dim rs As Recordset
Dim stLinkCriteriaIF As String
Dim stDocNameIF As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("job", dbOpenDynaset)

intAnswer = MsgBox("Would you like to close the " &
[Forms]![ApplicantProfile]![ApplicantAction]![Stattl] & _
" position at " & [Forms]![ApplicantProfile]![ApplicantAction]![Stacn] &
"?", vbYesNo _
, "To close or not to close?")
Select Case intAnswer
Case vbYes
While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
rs.Edit
rs("Jobsta") = "3"
rs.Update
End If
rs.MoveNext
Wend
End Select
End Sub
Ken Snell said:
This is the code of which I write:

While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
I = "Gotcha"
End If
rs.MoveNext
Wend

This code loops through the entire recordset to EOF. It does not remember
the specific record when the I variable is set, so when your next code wants
to edit the recordset, you can't move back to that record before editing.

My previous answer still applies. Your code that is trying to edit the
recordset rs is doing it at the EOF label, not on a record.
--
Ken Snell
<MS ACCESS MVP>


Julie said:
Ken,

I thought that the...Then I = "Gotcha"...acted as a flag and pulled the
record aside?


The record I am updating is:
Set rs = db.OpenRecordset("job", dbOpenSnapshot)
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] = rs("JobSer")

I need the field rs("Jobsta") to be updated to "3"

In other words: I need the code to
1) go to the [job] table
2) find the record where [job]![JobSer] matches the [StaJobSer] of the
record that I am currently viewing (there should only be one record)
3) update the [job]![Jobsta] to "3".

Does this make sense?

Thank you again for your help. I hope that you enjoyed your Thanksgiving.

Julie



Ken Snell said:
The code that precedes this is looping through the recordset, all the
Click to expand...
way
to
the EOF. So of course there is no current record when the rs.Edit code runs
(sorry...didn't see that initially).

Which record do you want to edit? Need to know that so that we can set this
last block of code up correctly.
--
Ken Snell
<MS ACCESS MVP>

Thank you Ken, we are getting closer I can feel it. Now I am getting the
error "No current record" and the line rs.edit is highlighted. Any ideas?

A snapshot recordset is not updatable.

Change dbOpenSnapshot to dbOpenDynaset.

Also, you need to insert rs.Edit and rs.Update:
If I = "Gotcha" Then
rs.Edit
rs("Jobsta") = "3"
rs.Update
End If


--
Ken Snell
<MS ACCESS MVP>
Hello Everyone,

I would like to enter the number 3 into a field in my table
Click to expand...
using
VBA.
So
far I have:

Private Sub Stastdt_Exit(Cancel As Integer)
'to close the job or not to close the job that is the question
Dim intAnswer As Integer
Dim db As Database
Dim rs As Recordset
Dim stLinkCriteriaIF As String
Dim stDocNameIF As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("job", dbOpenSnapshot)

intAnswer = MsgBox("Would you like to close the job?", vbYesNo, "To
close
or
not to close?")
Select Case intAnswer
Case vbYes
While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
I = "Gotcha"
End If
rs.MoveNext
Wend

If I = "Gotcha" Then
rs("Jobsta") = "3"
End If
End Select
End Sub

When I say yes to closing this job, I get the error:
Cannot update. Database object read only.

Does anyone know how I can get around this?

Thanks and have a Happy Thanksgiving!
Julie
Click to expand...
Click to expand...
Click to expand...
 
If I understand your question correctly, you want to know if your code would
change more than one record if more than one record meets the "If...Then"
criterion? The answer is yes.

--
Ken Snell
<MS ACCESS MVP>
Julie said:
Ken,
Thank you for coming back and checking to see if my problem has been solved
after all this time. (Because of the holiday I am getting a little more time
off.) The following code works beautifully, thank you for explaining. I
think I understand, but if you could answer one more question for me. Would
the following code work if I had multiple records to update and not just
one?

Private Sub Stastdt_Exit(Cancel As Integer)
'to close the job or not to close the job that is the question
Dim intAnswer As Integer
Dim db As Database
Dim rs As Recordset
Dim stLinkCriteriaIF As String
Dim stDocNameIF As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("job", dbOpenDynaset)

intAnswer = MsgBox("Would you like to close the " &
[Forms]![ApplicantProfile]![ApplicantAction]![Stattl] & _
" position at " & [Forms]![ApplicantProfile]![ApplicantAction]![Stacn] &
"?", vbYesNo _
, "To close or not to close?")
Select Case intAnswer
Case vbYes
While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
rs.Edit
rs("Jobsta") = "3"
rs.Update
End If
rs.MoveNext
Wend
End Select
End Sub
Ken Snell said:
This is the code of which I write:

While Not rs.EOF
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] =
rs("JobSer") Then
I = "Gotcha"
End If
rs.MoveNext
Wend

This code loops through the entire recordset to EOF. It does not remember
the specific record when the I variable is set, so when your next code wants
to edit the recordset, you can't move back to that record before editing.

My previous answer still applies. Your code that is trying to edit the
recordset rs is doing it at the EOF label, not on a record.
--
Ken Snell
<MS ACCESS MVP>


Julie said:
Ken,

I thought that the...Then I = "Gotcha"...acted as a flag and pulled the
record aside?


The record I am updating is:
Set rs = db.OpenRecordset("job", dbOpenSnapshot)
If [Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer] = rs("JobSer")

I need the field rs("Jobsta") to be updated to "3"

In other words: I need the code to
1) go to the [job] table
2) find the record where [job]![JobSer] matches the [StaJobSer] of the
record that I am currently viewing (there should only be one record)
3) update the [job]![Jobsta] to "3".

Does this make sense?

Thank you again for your help. I hope that you enjoyed your Thanksgiving.

Julie



The code that precedes this is looping through the recordset, all
Click to expand...
the
way
to
the EOF. So of course there is no current record when the rs.Edit code
runs
(sorry...didn't see that initially).

Which record do you want to edit? Need to know that so that we can set
this
last block of code up correctly.
--
Ken Snell
<MS ACCESS MVP>

Thank you Ken, we are getting closer I can feel it. Now I am
Click to expand...
getting
the
error "No current record" and the line rs.edit is highlighted. Any
ideas?

A snapshot recordset is not updatable.

Change dbOpenSnapshot to dbOpenDynaset.

Also, you need to insert rs.Edit and rs.Update:
If I = "Gotcha" Then
rs.Edit
rs("Jobsta") = "3"
rs.Update
End If


--
Ken Snell
<MS ACCESS MVP>
Hello Everyone,

I would like to enter the number 3 into a field in my table using
VBA.
So
far I have:

Private Sub Stastdt_Exit(Cancel As Integer)
'to close the job or not to close the job that is the question
Dim intAnswer As Integer
Dim db As Database
Dim rs As Recordset
Dim stLinkCriteriaIF As String
Dim stDocNameIF As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("job", dbOpenSnapshot)

intAnswer = MsgBox("Would you like to close the job?",
Click to expand...
vbYesNo,
"To
close
or
not to close?")
Select Case intAnswer
Case vbYes
While Not rs.EOF
If
Click to expand...
Click to expand...
[Forms]![ApplicantProfile]![ApplicantAction]![StaJobSer]
=
Click to expand...
Click to expand...
 
Back
Top