opening a form at specific record

  • Thread starter Thread starter Nathan Guill
  • Start date Start date
N

Nathan Guill

I would like to open another from from my main one at a specific record in
that second form. I've looked at the help in Access to attempt this, but it
just opens the form at the beginning of the table (not the record called out
in the main form). Here is what I have at the click event:

Access.DoCmd.OpenForm "PartNumbers", , Forms![PartNumbers]![txtPartNumber]
= Forms![Drawings]![txtPartNumber]

Any ideas? thanks.
 
Hi Nathan,

copy and paste this into your Click event between the lines Private Sub and
End Sub.

Dim stWhere as String
stWhere = "[txtPartNumber]=" & Me.txtPartNumber
DoCmd.OpenForm "PartNumbers",,,stWhere

HTH,
Jeff
 
I would like to open another from from my main one at a specific record in
that second form. I've looked at the help in Access to attempt this, but it
just opens the form at the beginning of the table (not the record called out
in the main form). Here is what I have at the click event:

Access.DoCmd.OpenForm "PartNumbers", , Forms![PartNumbers]![txtPartNumber]
= Forms![Drawings]![txtPartNumber]

Any ideas? thanks.
Click to expand...
The Where clause argument has to go in the Where clause argument
position (3 commas, not 2). And it must be written as a string.
If [txtPartNumber] is indeed a Number datatype field:

DoCmd.OpenForm "PartNumbers", , , "[txtPartNumber] = " &
Me![txtPartNumber]

If, however, [txtPartNumber] is a Text datatype field:
DoCmd.OpenForm "PartNumbers", , , "[txtPartNumber] = '" &
Me![txtPartNumber] & "'"

I've also simplified the rest of the where clause for you.
 
I'm not sure if it would make a difference, but the txtPartNumber fields on
both forms are text fields with variant data, not numeric. I copied this
code and when I click the button to launch this command, I get a dialog box
asking me what txtPartNumber is.

fredg said:
I would like to open another from from my main one at a specific record in
that second form. I've looked at the help in Access to attempt this, but it
just opens the form at the beginning of the table (not the record called out
in the main form). Here is what I have at the click event:

Access.DoCmd.OpenForm "PartNumbers", , Forms![PartNumbers]![txtPartNumber]
= Forms![Drawings]![txtPartNumber]

Any ideas? thanks.
Click to expand...
The Where clause argument has to go in the Where clause argument
position (3 commas, not 2). And it must be written as a string.
If [txtPartNumber] is indeed a Number datatype field:

DoCmd.OpenForm "PartNumbers", , , "[txtPartNumber] = " &
Me![txtPartNumber]

If, however, [txtPartNumber] is a Text datatype field:
DoCmd.OpenForm "PartNumbers", , , "[txtPartNumber] = '" &
Me![txtPartNumber] & "'"

I've also simplified the rest of the where clause for you.
Click to expand...
 
Thanks for the help. This put me on the right direction. I realized (after
sending my response to Fredg) that I should be querying the table, not the
form. So the where statement should read "[PartNumbers] = """ &
Me.txtPartNumbers & """"

Thanks again for the help.
 
I'm not sure if it would make a difference, but the txtPartNumber fields on
both forms are text fields with variant data, not numeric. I copied this
code and when I click the button to launch this command, I get a dialog box
asking me what txtPartNumber is.
* snipped *
If, however, [txtPartNumber] is a Text datatype field:
DoCmd.OpenForm "PartNumbers", , , "[txtPartNumber] = '" &
Me![txtPartNumber] & "'"

I've also simplified the rest of the where clause for you.
Click to expand...
Click to expand...

It's not the [txtPartNumber] on the forms but the [txtPartNumber]
field ** IN THE TABLE ** that is important here.
If it is Text Datatype, then the correct code and syntax is:

DoCmd.OpenForm "PartNumbers", , , "[txtPartNumber] = ' " &
Me![txtPartNumber] & " ' "

I've add a space between the single and double quotes above so that it
is clearer. Remove those spaces when you write the code.

If you are getting a prompt for [txtPartNumber] then Access doesn't
recognize it as a valid field. I have no idea what your form or table
field names are, so you'll need to work it out this way....

Here is a verbalized version of what the above code is supposed to do.
Change the field and table names to match your actual field and table
names.

Open the Form named "PartNumbers" where the field (in the table that
is the recordsource for the "PartNumbers" form ) named [txtPartNumber]
(which is a Text Datatype field) matches the control in this form
named [txtPartNumbers] .

If the value on the form of the [txtPartNumber] control is 1234 the
resultant Where clause will read:
"[txtPartNumber] = '1234'"
and that matching record will be displayed in the new form.
 
Back
Top