Run Time error with Look up Box

[Guest]

I have designed a form with a Combo Box to look up a specific member, which
also has a date field which is automatically updated when any changes are
made.

This is a Before Update Event Procedure on the Form and reads as follows:
Me.[Family Details Updated]=Date

There is also an After Update Event Procedure consisting of:
DoCmd.RunCommand acCmdSaveRecord
[Combo565].Requery

The problem is when i look up a member it works ok and then when I go to
look up another member I get the Runtime Error 3020-Update or CancelUpdate
without Addnew or Edit with reference to the line in the Before Update as
above-"Me.[Family etc.
I have to exit completely and restart the dbase to lookup again.

Any hekp would be greatly appreciated

 

[kingston via AccessMonster.com]

Allow Access to list the correct item when you type "Me." IOW, Me.[Family
Details Updated] is probably not valid. It should probably be Me.
Family_Details_Updated. Just make the correct selection when the list
appears after typing "Me." HTH

I have designed a form with a Combo Box to look up a specific member, which
also has a date field which is automatically updated when any changes are
made.

This is a Before Update Event Procedure on the Form and reads as follows:
Me.[Family Details Updated]=Date

There is also an After Update Event Procedure consisting of:
DoCmd.RunCommand acCmdSaveRecord
[Combo565].Requery

The problem is when i look up a member it works ok and then when I go to
look up another member I get the Runtime Error 3020-Update or CancelUpdate
without Addnew or Edit with reference to the line in the Before Update as
above-"Me.[Family etc.
I have to exit completely and restart the dbase to lookup again.

Any hekp would be greatly appreciated

 

[Guest]

Thanks Kingston
I am a little confused, nothing unusual for me but are you saying to type
"Me." in the Combo Lookup box or should I build this into the BeforeUpdate
Procedure and if so what would be the exact code.
Sorry to be a nuisance and thanks again

Allow Access to list the correct item when you type "Me." IOW, Me.[Family
Details Updated] is probably not valid. It should probably be Me.
Family_Details_Updated. Just make the correct selection when the list
appears after typing "Me." HTH

I have designed a form with a Combo Box to look up a specific member, which
also has a date field which is automatically updated when any changes are
made.

This is a Before Update Event Procedure on the Form and reads as follows:
Me.[Family Details Updated]=Date

There is also an After Update Event Procedure consisting of:
DoCmd.RunCommand acCmdSaveRecord
[Combo565].Requery

The problem is when i look up a member it works ok and then when I go to
look up another member I get the Runtime Error 3020-Update or CancelUpdate
without Addnew or Edit with reference to the line in the Before Update as
above-"Me.[Family etc.
I have to exit completely and restart the dbase to lookup again.

Any hekp would be greatly appreciated

 

[kingston via AccessMonster.com]

You're welcome. "Me." is a Visual Basic shortcut that is very handy in
procedures. It will not work in a control (combo box, text box, etc.) It
looks like you've used it before and I'm surprised that you haven't seen the
automatic behavior. In the VB editor, go to menu Tools -> Options (Editor
tab) and make sure Auto List Members is checked. Now, when you type "Me." in
the VB editor, a list of possibilities (in the proper syntax) should appear.
I guess you may be missing it too if your computer is really slow and you're
a really fast typist.

Thanks Kingston
I am a little confused, nothing unusual for me but are you saying to type
"Me." in the Combo Lookup box or should I build this into the BeforeUpdate
Procedure and if so what would be the exact code.
Sorry to be a nuisance and thanks again

Allow Access to list the correct item when you type "Me." IOW, Me.[Family
Details Updated] is probably not valid. It should probably be Me.

[quoted text clipped - 19 lines]

 

[Guest]

Thanks again Kingston. The Autolist Members was already checked. Perhaps I
should try and explain my problem a little better:
In Form design I have a BeforeUpdate Event procedure as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[Family Details Updated] = Date
End Sub

When I change any details in a record and click to the next record or Exit,
the Field FamilyDetailsUpdated is automatically updated to todays date. I can
then access the Combo Box Lookup and go to any Member I choose. However if I
do not click to say the next record and go straight to the Lookup Box, this
is when I get the Runtime error with reference to the Line -Me.[Family
Details Updated] = Date

I think what it is telling me is that I cannot access the Lookup Box until
the Updated Date is validated.

Have tried putting the same event in AfterUpdate without success.

Sorry if I am annoying you with this but any further advice would be
appreciated
Many Thanks

You're welcome. "Me." is a Visual Basic shortcut that is very handy in
procedures. It will not work in a control (combo box, text box, etc.) It
looks like you've used it before and I'm surprised that you haven't seen the
automatic behavior. In the VB editor, go to menu Tools -> Options (Editor
tab) and make sure Auto List Members is checked. Now, when you type "Me." in
the VB editor, a list of possibilities (in the proper syntax) should appear.
I guess you may be missing it too if your computer is really slow and you're
a really fast typist.

Thanks Kingston
I am a little confused, nothing unusual for me but are you saying to type
"Me." in the Combo Lookup box or should I build this into the BeforeUpdate
Procedure and if so what would be the exact code.
Sorry to be a nuisance and thanks again

Allow Access to list the correct item when you type "Me." IOW, Me.[Family
Details Updated] is probably not valid. It should probably be Me.

[quoted text clipped - 19 lines]

Any hekp would be greatly appreciated

 

[kingston via AccessMonster.com]

Try setting the default value of the control [Family Details Updated] to Date
() rather than invoking a procedure. Sorry, but I can't tell from your
postings how the combobox is tied to this date control (i.e. what
dependencies exist between the two controls).

Thanks again Kingston. The Autolist Members was already checked. Perhaps I
should try and explain my problem a little better:
In Form design I have a BeforeUpdate Event procedure as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[Family Details Updated] = Date
End Sub

When I change any details in a record and click to the next record or Exit,
the Field FamilyDetailsUpdated is automatically updated to todays date. I can
then access the Combo Box Lookup and go to any Member I choose. However if I
do not click to say the next record and go straight to the Lookup Box, this
is when I get the Runtime error with reference to the Line -Me.[Family
Details Updated] = Date

I think what it is telling me is that I cannot access the Lookup Box until
the Updated Date is validated.

Have tried putting the same event in AfterUpdate without success.

Sorry if I am annoying you with this but any further advice would be
appreciated
Many Thanks

You're welcome. "Me." is a Visual Basic shortcut that is very handy in
procedures. It will not work in a control (combo box, text box, etc.) It

[quoted text clipped - 16 lines]

 

[Guest]

Thanks again Kingston. Seting the Default value to todays date will only
work for a new record, and the date will not update when any existing records
are changed.

The Row source for the Combo Box is: SELECT [Main Table].AUTONUMBER, [Main
Table].Lname, [Main Table].Fname, [Main Table].[Street Number], [Main
Table].[Street Name], [Main Table].Suburb, [Main Table].[FName of Spouse],
[Main Table].[Lname of spouse] FROM [Main Table] ORDER BY [Main Table].Lname;

with an Afterupdate procedure as follows:
Private Sub Combo565_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[AUTONUMBER] = " & Str(Nz(Me![Combo565], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo565 = Null
End Sub
Is this of any help with a view to solving the problem?
I know you must be sick of me by now, so i will understand if you have
reached the end of your tether.
Thanks again

Try setting the default value of the control [Family Details Updated] to Date
() rather than invoking a procedure. Sorry, but I can't tell from your
postings how the combobox is tied to this date control (i.e. what
dependencies exist between the two controls).

Thanks again Kingston. The Autolist Members was already checked. Perhaps I
should try and explain my problem a little better:
In Form design I have a BeforeUpdate Event procedure as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.[Family Details Updated] = Date
End Sub

When I change any details in a record and click to the next record or Exit,
the Field FamilyDetailsUpdated is automatically updated to todays date. I can
then access the Combo Box Lookup and go to any Member I choose. However if I
do not click to say the next record and go straight to the Lookup Box, this
is when I get the Runtime error with reference to the Line -Me.[Family
Details Updated] = Date

I think what it is telling me is that I cannot access the Lookup Box until
the Updated Date is validated.

Have tried putting the same event in AfterUpdate without success.

Sorry if I am annoying you with this but any further advice would be
appreciated
Many Thanks

You're welcome. "Me." is a Visual Basic shortcut that is very handy in
procedures. It will not work in a control (combo box, text box, etc.) It

[quoted text clipped - 16 lines]

Any hekp would be greatly appreciated

 

[kingston via AccessMonster.com]

OK, I think I understand the problem now. Try adding an End If and an rs.
Close in the appropriate places in the AfterUpdate procedure. The error
message you're getting is related to editing or adding data to a DAO
recordset object without telling Access that you're trying to do so. Forget
about what I suggested about the Date; you probably had it right at the start.
I'll have more time in the morning to look at this more closely if necessary.
Remember:

Set rs = ...
rs.Edit
rs![Field] = Value
rs.Update
rs.Close

Thanks again Kingston. Seting the Default value to todays date will only
work for a new record, and the date will not update when any existing records
are changed.

The Row source for the Combo Box is: SELECT [Main Table].AUTONUMBER, [Main
Table].Lname, [Main Table].Fname, [Main Table].[Street Number], [Main
Table].[Street Name], [Main Table].Suburb, [Main Table].[FName of Spouse],
[Main Table].[Lname of spouse] FROM [Main Table] ORDER BY [Main Table].Lname;

with an Afterupdate procedure as follows:
Private Sub Combo565_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[AUTONUMBER] = " & Str(Nz(Me![Combo565], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo565 = Null
End Sub
Is this of any help with a view to solving the problem?
I know you must be sick of me by now, so i will understand if you have
reached the end of your tether.
Thanks again

Try setting the default value of the control [Family Details Updated] to Date
() rather than invoking a procedure. Sorry, but I can't tell from your

[quoted text clipped - 30 lines]

 

[Guest]

Sorry to be a pain, Kingston, but could you tell me, when you have the time,
the exact code I should write in the Afterupdate procedure of the Combo Box
and where it should appear in conjunction with the existing code as included
in my previous post.
Thanks for being so understanding

OK, I think I understand the problem now. Try adding an End If and an rs.
Close in the appropriate places in the AfterUpdate procedure. The error
message you're getting is related to editing or adding data to a DAO
recordset object without telling Access that you're trying to do so. Forget
about what I suggested about the Date; you probably had it right at the start.
I'll have more time in the morning to look at this more closely if necessary.
Remember:

Set rs = ...
rs.Edit
rs![Field] = Value
rs.Update
rs.Close

Thanks again Kingston. Seting the Default value to todays date will only
work for a new record, and the date will not update when any existing records
are changed.

The Row source for the Combo Box is: SELECT [Main Table].AUTONUMBER, [Main
Table].Lname, [Main Table].Fname, [Main Table].[Street Number], [Main
Table].[Street Name], [Main Table].Suburb, [Main Table].[FName of Spouse],
[Main Table].[Lname of spouse] FROM [Main Table] ORDER BY [Main Table].Lname;

with an Afterupdate procedure as follows:
Private Sub Combo565_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[AUTONUMBER] = " & Str(Nz(Me![Combo565], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Me.Combo565 = Null
End Sub
Is this of any help with a view to solving the problem?
I know you must be sick of me by now, so i will understand if you have
reached the end of your tether.
Thanks again

Try setting the default value of the control [Family Details Updated] to Date
() rather than invoking a procedure. Sorry, but I can't tell from your

[quoted text clipped - 30 lines]

Any hekp would be greatly appreciated

 

[kingston via AccessMonster.com]

Try this:

Private Sub Combo565_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[AUTONUMBER] = " & Str(Nz(Me![Combo565], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
rs.Close
'Me.Combo565 = Null
End Sub

I'm not sure that setting Combo565 to Null at the end is such a good idea so
I commented out that line. However, put it back in if you need it.

Sorry to be a pain, Kingston, but could you tell me, when you have the time,
the exact code I should write in the Afterupdate procedure of the Combo Box
and where it should appear in conjunction with the existing code as included
in my previous post.
Thanks for being so understanding

OK, I think I understand the problem now. Try adding an End If and an rs.
Close in the appropriate places in the AfterUpdate procedure. The error

[quoted text clipped - 39 lines]

 

[Guest]

Tried what you suggested, Kingston.
Now get a Compile Error: End If without Block If

Any other suggestions would be appreciated. Must be getting close and thanks

Try this:

Private Sub Combo565_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[AUTONUMBER] = " & Str(Nz(Me![Combo565], 0))
If Not rs.EOF Then
Me.Bookmark = rs.Bookmark
End If
rs.Close
'Me.Combo565 = Null
End Sub

I'm not sure that setting Combo565 to Null at the end is such a good idea so
I commented out that line. However, put it back in if you need it.

Sorry to be a pain, Kingston, but could you tell me, when you have the time,
the exact code I should write in the Afterupdate procedure of the Combo Box
and where it should appear in conjunction with the existing code as included
in my previous post.
Thanks for being so understanding

OK, I think I understand the problem now. Try adding an End If and an rs.
Close in the appropriate places in the AfterUpdate procedure. The error

[quoted text clipped - 39 lines]

Any hekp would be greatly appreciated

 

[kingston via AccessMonster.com]

Try it without the Not:

Private Sub Combo565_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[AUTONUMBER] = " & Str(Nz(Me![Combo565], 0))
If rs.EOF Then
Else
Me.Bookmark = rs.Bookmark
End If
rs.Close
'Me.Combo565 = Null
End Sub

Tried what you suggested, Kingston.
Now get a Compile Error: End If without Block If

Any other suggestions would be appreciated. Must be getting close and thanks

Try this:

[quoted text clipped - 25 lines]