select records using forms to allow editing/deleting

[Mabeline]

I am trying to set up a form that will allow the user to type in the key to a
record say Tag Id to then display that record (it is unique in the table) and
allow them to either edit or delete that record. Can anyone help me?

Thanks

 

[Wayne-I-M]

Hi Mabeline

Create an unbound text box (called FindRecord). Put this in the
AfterUpdate event

Private Sub FindRecord_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Tag Id ] = " & Str(Me![FindRecord])
Me.Bookmark = rs.Bookmark
End Sub

 

[Mabeline]

I am trying to set up a form that will allow the user to type in the key to a
record say Tag Id to then display that record (it is unique in the table) and
allow them to either edit or delete that record. Can anyone help me?

Thanks

This didn't do what I wanted it to do. Maybe I'm misunderstanding the
instructions or maybe my requirements weren't well written. The following is
what is happening using the instructions sent.

I dragged the field (called Id) from the database table (called testing) to
the form to create the unbound text box. I created the Event Procedure and
typed in the following:
Private Sub FindRecord_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Id] = " & Str(Me![FindRecord])
Me.Bookmark = rs.Bookmark
End Sub
It doesn't bring back the reocrd I want. It stays at the first record no
matter what Id number I type in.

It doesn't show the Event Procedure in the After Update field either & when
I go back into the event procedure it shows the code as above but it adds
another procedure Id_AfterUpdate.

All I want to do is select a record using the field Id (which is the unique
key to the table), display all the details on a form and allow the user to
either edit the details of the record or delete the records totally then save
the end result. This may be done a number of times before the form is closed.

Hope you can help further.

 

[Wayne-I-M]

Hi Mabeline

Is the form bound to the table ??
Open the form in design view and then open the properties box. Look in the
data column and you should see the table name in the source row. If you
don't select it from the dropdown list.

Next make sure that you don't already have the ID field on the form - if you
do and you bring it in again your will will not have the right name for the
control. Make sure that the box holding the ID data is called ID and nothing
else.

Right click the new box (call FindRecord) you made and open the properties
box. past the code I gave you into the AfterUpdate (code).
Check carefully the names in the code

I have looked at the code and run it on a small DB on my laptop and it works
fine so I may be explaining something wrong.

If you still cant get it to work paste the code you are using and I will
look at it again.

--
Wayne
Manchester, England.

I am trying to set up a form that will allow the user to type in the key to a
record say Tag Id to then display that record (it is unique in the table) and
allow them to either edit or delete that record. Can anyone help me?

Thanks

This didn't do what I wanted it to do. Maybe I'm misunderstanding the
instructions or maybe my requirements weren't well written. The following is
what is happening using the instructions sent.

I dragged the field (called Id) from the database table (called testing) to
the form to create the unbound text box. I created the Event Procedure and
typed in the following:
Private Sub FindRecord_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Id] = " & Str(Me![FindRecord])
Me.Bookmark = rs.Bookmark
End Sub
It doesn't bring back the reocrd I want. It stays at the first record no
matter what Id number I type in.

It doesn't show the Event Procedure in the After Update field either & when
I go back into the event procedure it shows the code as above but it adds
another procedure Id_AfterUpdate.

All I want to do is select a record using the field Id (which is the unique
key to the table), display all the details on a form and allow the user to
either edit the details of the record or delete the records totally then save
the end result. This may be done a number of times before the form is closed.

Hope you can help further.

 

[Wayne-I-M]

I have just re-read your post

"but it adds another procedure Id_AfterUpdate."

This tell me you have 2 fields with the ID data in and the new one you
created has another name.(access will do that automatically)

Delete the original ID field and rename the new one ID

--
Wayne
Manchester, England.

I am trying to set up a form that will allow the user to type in the key to a
record say Tag Id to then display that record (it is unique in the table) and
allow them to either edit or delete that record. Can anyone help me?

Thanks

This didn't do what I wanted it to do. Maybe I'm misunderstanding the
instructions or maybe my requirements weren't well written. The following is
what is happening using the instructions sent.

I dragged the field (called Id) from the database table (called testing) to
the form to create the unbound text box. I created the Event Procedure and
typed in the following:
Private Sub FindRecord_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Id] = " & Str(Me![FindRecord])
Me.Bookmark = rs.Bookmark
End Sub
It doesn't bring back the reocrd I want. It stays at the first record no
matter what Id number I type in.

It doesn't show the Event Procedure in the After Update field either & when
I go back into the event procedure it shows the code as above but it adds
another procedure Id_AfterUpdate.

All I want to do is select a record using the field Id (which is the unique
key to the table), display all the details on a form and allow the user to
either edit the details of the record or delete the records totally then save
the end result. This may be done a number of times before the form is closed.

Hope you can help further.

 

[Mabeline]

Hi Wayne I-M

Tried your suggestion but am still having some problems.

The Tag Number field is a text box titled FindRecord with Control Source Tag
NUmber. (Tag Number is defined in the table as text 15 characters long, it is
the primary ket with no duplicates allowed. Access 2000/2002 is what I am
using).

The code I put in as an event is:

Private Sub FindRecord_AfterUpdate()

Dim rs As Object

Set rs = Me.RecordSet.Clone
rs.FindFirst "[Tag Number] = ' " & Str(Me![FindRecord]) & "'"
If Not rs.eof then Me.Bookmark = rs.Bookmark
End Sub

When I start the form up there is already a record showing but I want a
blank form so that I can enter a valid Tag Number and the code will then
retrieve and display the record for me to edit or delete that record
retrieved.

When I enter a valid Tag Number in the Tag Number (FindRecord) field an
error message is displayed as follows:

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary ley, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try again".

Because I am keying an existing key value I believe the code is saying I am
trying enter another record with the same key rather than displaying the
record that matches the Tag Number I am entering. How do I start with a blank
record in the form then display the record that I want to delete or edit?

Thanks for you help and sorry to be a major pain.

Mabeline.

I have just re-read your post

"but it adds another procedure Id_AfterUpdate."

This tell me you have 2 fields with the ID data in and the new one you
created has another name.(access will do that automatically)

Delete the original ID field and rename the new one ID

--
Wayne
Manchester, England.

I am trying to set up a form that will allow the user to type in the key to a
record say Tag Id to then display that record (it is unique in the table) and
allow them to either edit or delete that record. Can anyone help me?

Thanks

This didn't do what I wanted it to do. Maybe I'm misunderstanding the
instructions or maybe my requirements weren't well written. The following is
what is happening using the instructions sent.

I dragged the field (called Id) from the database table (called testing) to
the form to create the unbound text box. I created the Event Procedure and
typed in the following:
Private Sub FindRecord_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Id] = " & Str(Me![FindRecord])
Me.Bookmark = rs.Bookmark
End Sub
It doesn't bring back the reocrd I want. It stays at the first record no
matter what Id number I type in.

It doesn't show the Event Procedure in the After Update field either & when
I go back into the event procedure it shows the code as above but it adds
another procedure Id_AfterUpdate.

All I want to do is select a record using the field Id (which is the unique
key to the table), display all the details on a form and allow the user to
either edit the details of the record or delete the records totally then save
the end result. This may be done a number of times before the form is closed.

Hope you can help further.

 

[Pieter Wijnen]

Try
Set rs = Me.RecordSetClone
rs.FindFirst "[Tag Number] = '" & Str(Me![FindRecord]) & "'"
If Not rs.NoMatch then Me.Bookmark = rs.Bookmark

HTH

Pieter

Hi Wayne I-M

Tried your suggestion but am still having some problems.

The Tag Number field is a text box titled FindRecord with Control Source
Tag
NUmber. (Tag Number is defined in the table as text 15 characters long, it
is
the primary ket with no duplicates allowed. Access 2000/2002 is what I am
using).

The code I put in as an event is:

Private Sub FindRecord_AfterUpdate()

Dim rs As Object

Set rs = Me.RecordSet.Clone
rs.FindFirst "[Tag Number] = ' " & Str(Me![FindRecord]) & "'"
If Not rs.eof then Me.Bookmark = rs.Bookmark
End Sub

When I start the form up there is already a record showing but I want a
blank form so that I can enter a valid Tag Number and the code will then
retrieve and display the record for me to edit or delete that record
retrieved.

When I enter a valid Tag Number in the Tag Number (FindRecord) field an
error message is displayed as follows:

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary ley, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try
again".

Because I am keying an existing key value I believe the code is saying I
am
trying enter another record with the same key rather than displaying the
record that matches the Tag Number I am entering. How do I start with a
blank
record in the form then display the record that I want to delete or edit?

Thanks for you help and sorry to be a major pain.

Mabeline.

I have just re-read your post

"but it adds another procedure Id_AfterUpdate."

This tell me you have 2 fields with the ID data in and the new one you
created has another name.(access will do that automatically)

Delete the original ID field and rename the new one ID

--
Wayne
Manchester, England.

:

I am trying to set up a form that will allow the user to type in the
key to a
record say Tag Id to then display that record (it is unique in the
table) and
allow them to either edit or delete that record. Can anyone help me?

Thanks

This didn't do what I wanted it to do. Maybe I'm misunderstanding the
instructions or maybe my requirements weren't well written. The
following is
what is happening using the instructions sent.

I dragged the field (called Id) from the database table (called
testing) to
the form to create the unbound text box. I created the Event Procedure
and
typed in the following:
Private Sub FindRecord_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Id] = " & Str(Me![FindRecord])
Me.Bookmark = rs.Bookmark
End Sub
It doesn't bring back the reocrd I want. It stays at the first record
no
matter what Id number I type in.

It doesn't show the Event Procedure in the After Update field either &
when
I go back into the event procedure it shows the code as above but it
adds
another procedure Id_AfterUpdate.

All I want to do is select a record using the field Id (which is the
unique
key to the table), display all the details on a form and allow the user
to
either edit the details of the record or delete the records totally
then save
the end result. This may be done a number of times before the form is
closed.

Hope you can help further.

 

[Mabeline]

Hi Pieter,

I will try this tomorrow and let you know how it goes.

Thanks for your help.


Regards

Mabeline

Try
Set rs = Me.RecordSetClone
rs.FindFirst "[Tag Number] = '" & Str(Me![FindRecord]) & "'"
If Not rs.NoMatch then Me.Bookmark = rs.Bookmark

HTH

Pieter

Hi Wayne I-M

Tried your suggestion but am still having some problems.

The Tag Number field is a text box titled FindRecord with Control Source
Tag
NUmber. (Tag Number is defined in the table as text 15 characters long, it
is
the primary ket with no duplicates allowed. Access 2000/2002 is what I am
using).

The code I put in as an event is:

Private Sub FindRecord_AfterUpdate()

Dim rs As Object

Set rs = Me.RecordSet.Clone
rs.FindFirst "[Tag Number] = ' " & Str(Me![FindRecord]) & "'"
If Not rs.eof then Me.Bookmark = rs.Bookmark
End Sub

When I start the form up there is already a record showing but I want a
blank form so that I can enter a valid Tag Number and the code will then
retrieve and display the record for me to edit or delete that record
retrieved.

When I enter a valid Tag Number in the Tag Number (FindRecord) field an
error message is displayed as follows:

"The changes you requested to the table were not successful because they
would create duplicate values in the index, primary ley, or relationship.
Change the data in the field or fields that contain duplicate data, remove
the index, or redefine the index to permit duplicate entries and try
again".

Because I am keying an existing key value I believe the code is saying I
am
trying enter another record with the same key rather than displaying the
record that matches the Tag Number I am entering. How do I start with a
blank
record in the form then display the record that I want to delete or edit?

Thanks for you help and sorry to be a major pain.

Mabeline.

I have just re-read your post

"but it adds another procedure Id_AfterUpdate."

This tell me you have 2 fields with the ID data in and the new one you
created has another name.(access will do that automatically)

Delete the original ID field and rename the new one ID

--
Wayne
Manchester, England.



:



:

I am trying to set up a form that will allow the user to type in the
key to a
record say Tag Id to then display that record (it is unique in the
table) and
allow them to either edit or delete that record. Can anyone help me?

Thanks

This didn't do what I wanted it to do. Maybe I'm misunderstanding the
instructions or maybe my requirements weren't well written. The
following is
what is happening using the instructions sent.

I dragged the field (called Id) from the database table (called
testing) to
the form to create the unbound text box. I created the Event Procedure
and
typed in the following:
Private Sub FindRecord_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Id] = " & Str(Me![FindRecord])
Me.Bookmark = rs.Bookmark
End Sub
It doesn't bring back the reocrd I want. It stays at the first record
no
matter what Id number I type in.

It doesn't show the Event Procedure in the After Update field either &
when
I go back into the event procedure it shows the code as above but it
adds
another procedure Id_AfterUpdate.

All I want to do is select a record using the field Id (which is the
unique
key to the table), display all the details on a form and allow the user
to
either edit the details of the record or delete the records totally
then save
the end result. This may be done a number of times before the form is
closed.

Hope you can help further.

 

[Mabeline]

Hi Wayne I-M

I haven't replied before this because I was still having problems getting
your suggestion to work. Did a lot of reading and found that I could do what
I wanted using a separate form (in modal mode) which allows me to enter the
Tag Number that I want the record for and this form then calls another form
using a macro which allows me to update or delete the record. Having said
this I am still having a problem in that the first form (in modal mode) is
trying to create a record using the same key to the table. Even though I can
update or delete the record in the second form I am getting errors on the
screen when returning to the first form where it is trying to create the same
record and because the Tag Number field is the key to the table it displays
messages.

I was wondering if you could email the small database you created to me so
that I could see how you got yours working and then I could use it as a
template for my own. My email address is [email protected].

Hope you don't mind this request. And thank you very much for your help and
patience.


Regards
Mabeline.

I have just re-read your post

"but it adds another procedure Id_AfterUpdate."

This tell me you have 2 fields with the ID data in and the new one you
created has another name.(access will do that automatically)

Delete the original ID field and rename the new one ID

--
Wayne
Manchester, England.

I am trying to set up a form that will allow the user to type in the key to a
record say Tag Id to then display that record (it is unique in the table) and
allow them to either edit or delete that record. Can anyone help me?

Thanks

This didn't do what I wanted it to do. Maybe I'm misunderstanding the
instructions or maybe my requirements weren't well written. The following is
what is happening using the instructions sent.

I dragged the field (called Id) from the database table (called testing) to
the form to create the unbound text box. I created the Event Procedure and
typed in the following:
Private Sub FindRecord_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[Id] = " & Str(Me![FindRecord])
Me.Bookmark = rs.Bookmark
End Sub
It doesn't bring back the reocrd I want. It stays at the first record no
matter what Id number I type in.

It doesn't show the Event Procedure in the After Update field either & when
I go back into the event procedure it shows the code as above but it adds
another procedure Id_AfterUpdate.

All I want to do is select a record using the field Id (which is the unique
key to the table), display all the details on a form and allow the user to
either edit the details of the record or delete the records totally then save
the end result. This may be done a number of times before the form is closed.

Hope you can help further.