SELECT A RECORD IN ONE FORM AND GO TO ANOTHER FORM

[Guest]

I have two forms. One form has a search command button tide to a macro.

When the user clicks on the search command it prompts them to enter a last name. Then it goes to another form and lists all the possibilities.

If the individual is in the second form the user then clicks on the the specific id. Then the main form opens, the control go to the ID field and the find command option appears. The the user uses ctrl V to past the user ID that that selected from the other form clicks find and then clicks the cancel button the find to close it out.

This is all running out of a macro i.e.

RunCommand = Copy the user ID
OpenForm = Open the form main form so you can edit various pieces of information.
GoToControl = Goes to the specific user ID field on the main form.
RunCommand = Initiates the find command option.

Question is there a simpler way to do this. ie. if the user selects an individual in the second form that it goes straight to that user on the main form?

Thanks in advance

 

[John]

Vic, what about staying on the one form for doing your
searching? I have an access form that uses a drop down
listing of names (ascending) that when selected from the
drop list makes the form go to that record and displays
the info on the form. Draw back is the number of names in
the drop list. The more there are the slower it gets. I
referring to 1000's. What I did was to place a small
button next to the drop list that will display a small
form with A-Z buttons on it which when selected will now
only show the names in the drop list with that letter.
Use the bookmark method to go to the selected record.
Here is part of it below.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[YourTableID] = " & Str(Me!
[comboboxname])
Me.Bookmark = rs.Bookmark

Hope this works.
*** John

-----Original Message-----
I have two forms. One form has a search command button tide to a macro.

When the user clicks on the search command it prompts

them to enter a last name. Then it goes to another form
and lists all the possibilities.

If the individual is in the second form the user then

clicks on the the specific id. Then the main form opens,
the control go to the ID field and the find command option
appears. The the user uses ctrl V to past the user ID
that that selected from the other form clicks find and
then clicks the cancel button the find to close it out.

This is all running out of a macro i.e.

RunCommand = Copy the user ID
OpenForm = Open the form main form so you can edit various pieces of information.
GoToControl = Goes to the specific user ID field on the main form.
RunCommand = Initiates the find command option.

Question is there a simpler way to do this. ie. if the

user selects an individual in the second form that it goes
straight to that user on the main form?

 

[Guest]

I'm not quite sure how to implement something like that. It sounds somewhat what I'm looking for.

Vic, what about staying on the one form for doing your
searching? I have an access form that uses a drop down
listing of names (ascending) that when selected from the
drop list makes the form go to that record and displays
the info on the form. Draw back is the number of names in
the drop list. The more there are the slower it gets. I
referring to 1000's. What I did was to place a small
button next to the drop list that will display a small
form with A-Z buttons on it which when selected will now
only show the names in the drop list with that letter.
Use the bookmark method to go to the selected record.
Here is part of it below.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[YourTableID] = " & Str(Me!
[comboboxname])
Me.Bookmark = rs.Bookmark

Hope this works.
*** John

-----Original Message-----
I have two forms. One form has a search command button tide to a macro.

When the user clicks on the search command it prompts

them to enter a last name. Then it goes to another form
and lists all the possibilities.

If the individual is in the second form the user then

clicks on the the specific id. Then the main form opens,
the control go to the ID field and the find command option
appears. The the user uses ctrl V to past the user ID
that that selected from the other form clicks find and
then clicks the cancel button the find to close it out.

This is all running out of a macro i.e.

RunCommand = Copy the user ID
OpenForm = Open the form main form so you can edit various pieces of information.
GoToControl = Goes to the specific user ID field on the main form.
RunCommand = Initiates the find command option.

Question is there a simpler way to do this. ie. if the

user selects an individual in the second form that it goes
straight to that user on the main form?

Thanks in advance
.

 

[John]

It can be rather easy once you see how it works and begin
to develop it to suit your needs.

1) place a combo box on the form (unbound)
2) go to the properties and make you query from the table
you want to search.
3) in the query, one column should be the primary field
(generally the ID field)
4) add whatever other columns you need
5) back on the properties, you can hide (zero column
width) the ID field but it is the bound field

This is a rough sketch of what to do. The code in the
other reply would go in the AfterUpdate event of the combo
box.

The small button next to the drop list just brings up a
small form with buttons on it for each letter of the
alphabet. This should be easy to make and place. Then
behind each button is a query. An example is:
Forms![MainFormName]![ComboBoxName].RowSource = _
"SELECT " & _
"Field1 name, " & _
"Field2 name, " & _
"Field3 name, " & _
"Field4 name" & _
"FROM YourTableName" & _
"WHERE (((Field3) LIKE 'A*')) " & _
"ORDER BY Field2 ASC;"
I used gerneric names and the number of fields you might
want could be different.

A good way to get you started using the combo box as
search tool is to use the wizard on the tool box. Have it
on when you select the combo box and when it is placed on
the form, it will give you steps to go thru. One of which
is the selecting from list and finding the record.

Good luck.

*** John

-----Original Message-----
I'm not quite sure how to implement something like that.

It sounds somewhat what I'm looking for.

Vic, what about staying on the one form for doing your
searching? I have an access form that uses a drop down
listing of names (ascending) that when selected from the
drop list makes the form go to that record and displays
the info on the form. Draw back is the number of names in
the drop list. The more there are the slower it gets. I
referring to 1000's. What I did was to place a small
button next to the drop list that will display a small
form with A-Z buttons on it which when selected will now
only show the names in the drop list with that letter.
Use the bookmark method to go to the selected record.
Here is part of it below.

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[YourTableID] = " & Str(Me!
[comboboxname])
Me.Bookmark = rs.Bookmark

Hope this works.
*** John

-----Original Message-----
I have two forms. One form has a search command

button
tide to a macro.

When the user clicks on the search command it prompts

them to enter a last name. Then it goes to another form
and lists all the possibilities.

If the individual is in the second form the user then

clicks on the the specific id. Then the main form opens,
the control go to the ID field and the find command option
appears. The the user uses ctrl V to past the user ID
that that selected from the other form clicks find and
then clicks the cancel button the find to close it out.

This is all running out of a macro i.e.

RunCommand = Copy the user ID
OpenForm = Open the form main form so you can edit various pieces of information.
GoToControl = Goes to the specific user ID field on

the
main form.

RunCommand = Initiates the find command option.

Question is there a simpler way to do this. ie. if

the
user selects an individual in the second form that it goes
straight to that user on the main form?

Thanks in advance
.

.

 

[Albert D. Kallal]

Sounds to me like you have 3 forms to accomplish this.

I would consider using one form for the searching (and it will display the
results).

However, either way, the solution you need is when the user clicks on a
record, the "main edit" form appears. You can place a button on the
continues form, or even use a double click event. The code to open the form
to the ONE record is:


docmd.OpenForm "MyMainForm",,,"id = " & me!id

The above assume you have a key id field called ID.

Here is some screen shots and ideas of what I mean by using ONE form for the
search (and the search results). I don't think you need 3 forms...but not a
big deal....
Here is some screen shots of searching in ms-access:

http://www.attcanada.net/~kallal.msn/Search/index.html

 

[Guest]

Exceptional.

Thank You