Retrieve and Edit a record

[Guest]

I want to retrieve an existing record from a table and display for editting
(in an update/add screen). I'm not sure where to start with the retrieval
nor how to display the record for editting once I've found the match.

Any ideas from the experts?

Cheers,
Leslie

 

[Rick B]

Well, first create a form in which you will add and edit records.

Then decide how you wish to "find" the correct record. The easiest way is
to use the combo-box wizard and put a drop-down combo-box at the top of your
form. Allow your users to locate the customer name, or invoice number, or
record number (you don't tell us what is in your table) that they wish to
pull up. That combo-box wizard includes the option to "find a record in
your form based on the value selected in the combo-box."

 

[Guest]

Hi Rick,

I have a table "VENDORS" and I don't think a Combo Box will work as we will
probably have too many "Vendors" to choose from. I would rather have the
user type in a value on the VendorID, check to see if that Value exists. If
it exists ask them if they want to edit this record or if they want to enter
a new record or try again. I've got the syntax for reading the VendorID to
see if exists (on Before Update, partial of the message box, but I don't
have the syntax to actually display the record (all fields, e.g. contact
name, phone, etc.) for editting.

I do have the form set up for "Data Entry" so that it doesn't normally
display existing records, but in this case I want to display a record if the
user so chooses.

I hope that gives enough info the help further.

Cheers,
Leslie

 

[Rick B]

You can type a value in a combo box.

Sounds like you have a pretty good grasp of code and you're writing a more
advanced form tha tI mentioned. Your initial post did not revela your level
of expert knowledge. I had assumed you were fairly new and were working
directly in the tables as many new users do. Obviously you're past that.

Personally, I would have my form default to a new record. I'd code my
"find" field to locate a record. If not found, I believe it would issue an
error and leave the user in the add mode.

As far as going to the record, your code would look something like...

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = " & Str(Me![Combo5])
Me.Bookmark = rs.Bookmark
End Sub

This was created using the wizard and a combo-box, but it would work with an
unbound text box as well.

 

[Guest]

Thank you Rick,

You give me more credit than I deserve. I've just been hacking around in
Access the last week -- I used to code in FoxPro and DBIII+/DBIV -- many
years ago.

A couple quick little questions. If a combo box allows typing, will it put
the cursor at the location in the table and display the nearby say 6 records
as you are typing? (e.g. if I type B it goes to the first entry with a "B"
then I type an "E" after the "B" and have "BE" so it takes me to the first
entry begining with "BE", etc.)

Second, you said I can set the form default to "New Record"... How do I do
that. I can't find anything in the Form Properties.

I'll give your code a try and see if I can't move forward!

Thanks again!
Leslie

"Rick B wrote"

You can type a value in a combo box.

Sounds like you have a pretty good grasp of code and you're writing a more
advanced form tha tI mentioned. Your initial post did not revela your level
of expert knowledge. I had assumed you were fairly new and were working
directly in the tables as many new users do. Obviously you're past that.

Personally, I would have my form default to a new record. I'd code my
"find" field to locate a record. If not found, I believe it would issue an
error and leave the user in the add mode.

As far as going to the record, your code would look something like...

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = " & Str(Me![Combo5])
Me.Bookmark = rs.Bookmark
End Sub

This was created using the wizard and a combo-box, but it would work with an
unbound text box as well.


--
Rick B

Hi Rick,

I have a table "VENDORS" and I don't think a Combo Box will work as we will
probably have too many "Vendors" to choose from. I would rather have the
user type in a value on the VendorID, check to see if that Value exists. If
it exists ask them if they want to edit this record or if they want to enter
a new record or try again. I've got the syntax for reading the VendorID to
see if exists (on Before Update, partial of the message box, but I don't
have the syntax to actually display the record (all fields, e.g. contact
name, phone, etc.) for editting.

I do have the form set up for "Data Entry" so that it doesn't normally
display existing records, but in this case I want to display a record if the
user so chooses.

I hope that gives enough info the help further.

Cheers,
Leslie

 

[Guest]

Leslie or Rick,
In a database I have created, I have a form. I am trying to retrieve and
edit a record with a separate combo box on top based on an ID number and it
is not working. I used the wizard to do this. If i am in the form and
switching from design view to form view it works but if i close out and
launch the form it does not work. I have sifted through the help sections and
am not able to come up with a solution. Any suggestions?
-Stephanie

 

[Rick B]

When you type in a combo-box, the cursor jumps down the list as you type.
So if you have a list os states, for example, typing a "T" will take you to
Tennessee.

To default to a new record, put code in your form's OPEN event...
DoCmd.GoToRecord,,acNewRec


--
Rick B

Thank you Rick,

You give me more credit than I deserve. I've just been hacking around in
Access the last week -- I used to code in FoxPro and DBIII+/DBIV -- many
years ago.

A couple quick little questions. If a combo box allows typing, will it put
the cursor at the location in the table and display the nearby say 6 records
as you are typing? (e.g. if I type B it goes to the first entry with a "B"
then I type an "E" after the "B" and have "BE" so it takes me to the first
entry begining with "BE", etc.)

Second, you said I can set the form default to "New Record"... How do I do
that. I can't find anything in the Form Properties.

I'll give your code a try and see if I can't move forward!

Thanks again!
Leslie

"Rick B wrote"

You can type a value in a combo box.

Sounds like you have a pretty good grasp of code and you're writing a more
advanced form tha tI mentioned. Your initial post did not revela your level
of expert knowledge. I had assumed you were fairly new and were working
directly in the tables as many new users do. Obviously you're past that.

Personally, I would have my form default to a new record. I'd code my
"find" field to locate a record. If not found, I believe it would issue an
error and leave the user in the add mode.

As far as going to the record, your code would look something like...

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = " & Str(Me![Combo5])
Me.Bookmark = rs.Bookmark
End Sub

This was created using the wizard and a combo-box, but it would work with an
unbound text box as well.


--
Rick B

Hi Rick,

I have a table "VENDORS" and I don't think a Combo Box will work as we will
probably have too many "Vendors" to choose from. I would rather have the
user type in a value on the VendorID, check to see if that Value

exists.
If

it exists ask them if they want to edit this record or if they want to enter
a new record or try again. I've got the syntax for reading the

VendorID
to

see if exists (on Before Update, partial of the message box, but I don't
have the syntax to actually display the record (all fields, e.g. contact
name, phone, etc.) for editting.

I do have the form set up for "Data Entry" so that it doesn't normally
display existing records, but in this case I want to display a record

if
the

user so chooses.

I hope that gives enough info the help further.

Cheers,
Leslie

:

Well, first create a form in which you will add and edit records.

Then decide how you wish to "find" the correct record. The easiest

way
is

to use the combo-box wizard and put a drop-down combo-box at the top

of
your

form. Allow your users to locate the customer name, or invoice

number,
or

record number (you don't tell us what is in your table) that they

wish
to

pull up. That combo-box wizard includes the option to "find a record in
your form based on the value selected in the combo-box."

--
Rick B



I want to retrieve an existing record from a table and display for
editting
(in an update/add screen). I'm not sure where to start with the retrieval
nor how to display the record for editting once I've found the match.

Any ideas from the experts?

Cheers,
Leslie

 

[Guest]

Thanks Rick,

You've been a gem!

Leslie

When you type in a combo-box, the cursor jumps down the list as you type.
So if you have a list os states, for example, typing a "T" will take you to
Tennessee.

To default to a new record, put code in your form's OPEN event...
DoCmd.GoToRecord,,acNewRec


--
Rick B

Thank you Rick,

You give me more credit than I deserve. I've just been hacking around in
Access the last week -- I used to code in FoxPro and DBIII+/DBIV -- many
years ago.

A couple quick little questions. If a combo box allows typing, will it put
the cursor at the location in the table and display the nearby say 6 records
as you are typing? (e.g. if I type B it goes to the first entry with a "B"
then I type an "E" after the "B" and have "BE" so it takes me to the first
entry begining with "BE", etc.)

Second, you said I can set the form default to "New Record"... How do I do
that. I can't find anything in the Form Properties.

I'll give your code a try and see if I can't move forward!

Thanks again!
Leslie

"Rick B wrote"

You can type a value in a combo box.

Sounds like you have a pretty good grasp of code and you're writing a more
advanced form tha tI mentioned. Your initial post did not revela your level
of expert knowledge. I had assumed you were fairly new and were working
directly in the tables as many new users do. Obviously you're past that.

Personally, I would have my form default to a new record. I'd code my
"find" field to locate a record. If not found, I believe it would issue an
error and leave the user in the add mode.

As far as going to the record, your code would look something like...

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = " & Str(Me![Combo5])
Me.Bookmark = rs.Bookmark
End Sub

This was created using the wizard and a combo-box, but it would work with an
unbound text box as well.


--
Rick B



Hi Rick,

I have a table "VENDORS" and I don't think a Combo Box will work as we
will
probably have too many "Vendors" to choose from. I would rather have the
user type in a value on the VendorID, check to see if that Value exists.
If
it exists ask them if they want to edit this record or if they want to
enter
a new record or try again. I've got the syntax for reading the VendorID
to
see if exists (on Before Update, partial of the message box, but I don't
have the syntax to actually display the record (all fields, e.g. contact
name, phone, etc.) for editting.

I do have the form set up for "Data Entry" so that it doesn't normally
display existing records, but in this case I want to display a record if
the
user so chooses.

I hope that gives enough info the help further.

Cheers,
Leslie

:

Well, first create a form in which you will add and edit records.

Then decide how you wish to "find" the correct record. The easiest way
is
to use the combo-box wizard and put a drop-down combo-box at the top of
your
form. Allow your users to locate the customer name, or invoice number,
or
record number (you don't tell us what is in your table) that they wish
to
pull up. That combo-box wizard includes the option to "find a record in
your form based on the value selected in the combo-box."

--
Rick B



I want to retrieve an existing record from a table and display for
editting
(in an update/add screen). I'm not sure where to start with the
retrieval
nor how to display the record for editting once I've found the match.

Any ideas from the experts?

Cheers,
Leslie