Using VBA to enter a value into a table

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
 
K

Ken Snell

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
 
J

Julie

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...
 
K

Ken Snell

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...
 
J

Julie

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...
 
K

Ken Snell

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...
 
J

Julie

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...
 
K

Ken Snell

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Export to PDF 0
Word macro that can call cells out of Excel for a find and replace 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Error 3020 for VB: Update of CancelUpdate without AddNew or Edit? 6
Need to create a dynamic recordset using generic field names 8
count of records in a table in external database 2

Top