Open a form at a specific record

[Guest]

This is probably simple, but I am a bit of a beginner. I am trying to create
a button on a form - which I use as an opening men for the database - that
will open another form at a specific record. The forms hold the records of
various organisations and their names are the primary key. I know how to
build queries to get specific information, but not how to open an entire form
at a specific record.

 

[Allen Browne]

Unless the target form is already open, you can open it filtered to just the
one record by using the WhereCondition argument of OpenForm.

For example, if you have a Number field named ID:

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.ID) then
strWhere = "ID = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If

If you acutally want to open the form unfiltered, but move to the desired
record, use FindFirst on the RecordsetClone of the form, and then match
bookmark.

Either way, the WhereCondition or FindFirst string is anything that you
could validly put in the WHERE clause of a query.

 

[Guest]

Thank you, but I did warn you I am a beginner!

I assume I enter this by going to "Build Event" for the button. But I am
not sure where in the code you have written I put the name of the form to be
opened, the field of the record to search and the actual name in that
particular field on to produce the particular record. Below I have used
"registered clubs" for the name of the form, "actual club" for the name of
the field and "blue and whites" for the entry in the field for the record
that I am trying to produce, in the code that you gave me, but it doesn't
work so I am obviously not got the names in the right places.

Dim strWhere As String

If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.[actual club]) then
strWhere = "[actual club] = " & Me.[blue and whites]
DoCmd.OpenForm "[registered clubs]", WhereCondition:=strWhere
End If

I have tried various permutations without success. With the above I get
error message "Access can't find field '|' referred to in your expression.

Any ideas?

 

[Allen Browne]

If you are looking for the text "blue and whites" in the field [actual
club]:
strWhere = "[actual club] = ""blue and whites"""
DoCmd.OpenForm "registered clubs", WhereCondition:=strWhere

If that still fails, indicate the error number, message, and which line
fails.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you, but I did warn you I am a beginner!

I assume I enter this by going to "Build Event" for the button. But I am
not sure where in the code you have written I put the name of the form to
be
opened, the field of the record to search and the actual name in that
particular field on to produce the particular record. Below I have used
"registered clubs" for the name of the form, "actual club" for the name of
the field and "blue and whites" for the entry in the field for the record
that I am trying to produce, in the code that you gave me, but it doesn't
work so I am obviously not got the names in the right places.

Dim strWhere As String

If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.[actual club]) then
strWhere = "[actual club] = " & Me.[blue and whites]
DoCmd.OpenForm "[registered clubs]", WhereCondition:=strWhere
End If

I have tried various permutations without success. With the above I get
error message "Access can't find field '|' referred to in your expression.

Any ideas?

--
Hagan

Unless the target form is already open, you can open it filtered to just
the
one record by using the WhereCondition argument of OpenForm.

For example, if you have a Number field named ID:

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.ID) then
strWhere = "ID = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If

If you acutally want to open the form unfiltered, but move to the desired
record, use FindFirst on the RecordsetClone of the form, and then match
bookmark.

Either way, the WhereCondition or FindFirst string is anything that you
could validly put in the WHERE clause of a query.

 

[Guest]

As I write in: strWhere = "[actual club] = "blue
I am interupted, this line goes red, with the top line: Private Sub SDB
Click () becoming highlighted
and I get error message Compile error: Expected: end of statement.
This is the full code I am putting in, including the first two lines which
are already there before I start.

Private Sub SDB_Click()
On Error GoTo Err_SDB_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.[actual club]) Then
strWhere ="[actual club]= "blue and whites"
DoCmd.OpenForm "registered clubs", WhereCondition:=strWhere
End If

--
Hagan

Thank you, but I did warn you I am a beginner!

I assume I enter this by going to "Build Event" for the button. But I am
not sure where in the code you have written I put the name of the form to be
opened, the field of the record to search and the actual name in that
particular field on to produce the particular record. Below I have used
"registered clubs" for the name of the form, "actual club" for the name of
the field and "blue and whites" for the entry in the field for the record
that I am trying to produce, in the code that you gave me, but it doesn't
work so I am obviously not got the names in the right places.

Dim strWhere As String

If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.[actual club]) then
strWhere = "[actual club] = " & Me.[blue and whites]
DoCmd.OpenForm "[registered clubs]", WhereCondition:=strWhere
End If

I have tried various permutations without success. With the above I get
error message "Access can't find field '|' referred to in your expression.

Any ideas?

--
Hagan

Unless the target form is already open, you can open it filtered to just the
one record by using the WhereCondition argument of OpenForm.

For example, if you have a Number field named ID:

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.ID) then
strWhere = "ID = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If

If you acutally want to open the form unfiltered, but move to the desired
record, use FindFirst on the RecordsetClone of the form, and then match
bookmark.

Either way, the WhereCondition or FindFirst string is anything that you
could validly put in the WHERE clause of a query.

 

[Allen Browne]

Your quotes do not match what we suggested for you to type in.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

As I write in: strWhere = "[actual club] = "blue
I am interupted, this line goes red, with the top line: Private Sub SDB
Click () becoming highlighted
and I get error message Compile error: Expected: end of statement.
This is the full code I am putting in, including the first two lines which
are already there before I start.

Private Sub SDB_Click()
On Error GoTo Err_SDB_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.[actual club]) Then
strWhere ="[actual club]= "blue and whites"
DoCmd.OpenForm "registered clubs", WhereCondition:=strWhere
End If

--
Hagan

Thank you, but I did warn you I am a beginner!

I assume I enter this by going to "Build Event" for the button. But I am
not sure where in the code you have written I put the name of the form to
be
opened, the field of the record to search and the actual name in that
particular field on to produce the particular record. Below I have used
"registered clubs" for the name of the form, "actual club" for the name
of
the field and "blue and whites" for the entry in the field for the record
that I am trying to produce, in the code that you gave me, but it doesn't
work so I am obviously not got the names in the right places.

Dim strWhere As String

If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.[actual club]) then
strWhere = "[actual club] = " & Me.[blue and whites]
DoCmd.OpenForm "[registered clubs]", WhereCondition:=strWhere
End If

I have tried various permutations without success. With the above I get
error message "Access can't find field '|' referred to in your
expression.

Any ideas?

--
Hagan

Unless the target form is already open, you can open it filtered to
just the
one record by using the WhereCondition argument of OpenForm.

For example, if you have a Number field named ID:

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.ID) then
strWhere = "ID = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If

If you acutally want to open the form unfiltered, but move to the
desired
record, use FindFirst on the RecordsetClone of the form, and then match
bookmark.

Either way, the WhereCondition or FindFirst string is anything that you
could validly put in the WHERE clause of a query.

This is probably simple, but I am a bit of a beginner. I am trying
to
create
a button on a form - which I use as an opening men for the database -
that
will open another form at a specific record. The forms hold the
records
of
various organisations and their names are the primary key. I know
how to
build queries to get specific information, but not how to open an
entire
form
at a specific record.

 

[Guest]

Yes sorry, I see my mistake, it works fine, thank you very much.

Is there anything simple I can add to the code to close the original form
before opening the next one? I want my database to work a bit like a website
with buttons to navigate from one place to another, without a load of forms
left open.
--
Hagan

Your quotes do not match what we suggested for you to type in.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

As I write in: strWhere = "[actual club] = "blue
I am interupted, this line goes red, with the top line: Private Sub SDB
Click () becoming highlighted
and I get error message Compile error: Expected: end of statement.
This is the full code I am putting in, including the first two lines which
are already there before I start.

Private Sub SDB_Click()
On Error GoTo Err_SDB_Click
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.[actual club]) Then
strWhere ="[actual club]= "blue and whites"
DoCmd.OpenForm "registered clubs", WhereCondition:=strWhere
End If

--
Hagan

Thank you, but I did warn you I am a beginner!

I assume I enter this by going to "Build Event" for the button. But I am
not sure where in the code you have written I put the name of the form to
be
opened, the field of the record to search and the actual name in that
particular field on to produce the particular record. Below I have used
"registered clubs" for the name of the form, "actual club" for the name
of
the field and "blue and whites" for the entry in the field for the record
that I am trying to produce, in the code that you gave me, but it doesn't
work so I am obviously not got the names in the right places.

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.[actual club]) then
strWhere = "[actual club] = " & Me.[blue and whites]
DoCmd.OpenForm "[registered clubs]", WhereCondition:=strWhere
End If

I have tried various permutations without success. With the above I get
error message "Access can't find field '|' referred to in your
expression.

Any ideas?

--
Hagan


:

Unless the target form is already open, you can open it filtered to
just the
one record by using the WhereCondition argument of OpenForm.

For example, if you have a Number field named ID:

Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save first.
If Not IsNull(Me.ID) then
strWhere = "ID = " & Me.ID
DoCmd.OpenForm "Form2", WhereCondition:=strWhere
End If

If you acutally want to open the form unfiltered, but move to the
desired
record, use FindFirst on the RecordsetClone of the form, and then match
bookmark.

Either way, the WhereCondition or FindFirst string is anything that you
could validly put in the WHERE clause of a query.

This is probably simple, but I am a bit of a beginner. I am trying
to
create
a button on a form - which I use as an opening men for the database -
that
will open another form at a specific record. The forms hold the
records
of
various organisations and their names are the primary key. I know
how to
build queries to get specific information, but not how to open an
entire
form
at a specific record.

 

[Allen Browne]

After the OpenForm, add the line:

DoCmd.Close acForm, Me.Name

 

[Guest]

Thank you for your help, which has completely solved the problem.

Is there anything simple that I can add to the code to get the original menu
form to close before taking me to the specific record on the next form?

 

[Allen Browne]

I suggest you put the close last, after everything else has completed.

Access is very forgiving, and it will continue to run the code in the form
that you just "closed", but I'm not sure that is a logical approach.