Open Form to Specific Record

O

Orv

I have placed a command button on a form, that when clicked prompts for a
number, then should goto that record. Here is my code:

Command Button on click: =OpenFormWithInput()

Code:


Option Explicit

Function OpenFormWithInput()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter RTF Number (11111.)."
Title = "OPEN RTF FORM"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmRTF", , "[RTFNumber]='" & Answer & "' "
Else
DoCmd.OpenForm "frmRTF"
End If
End Function

When I open the form and click on the button, I enter a number (with or w/o
the hyphen) in the prompt and click OK, it then goes to the first record,
regardless of the nuber entered.

The RTFNumber field is formatted as 00-0000 and has input mask as such
99\-9999 not that I think this matters. I think there is an error in my code
more particulary with the DoCmd OpenForm.

Any help is appreciated,

Orv
 
M

Marshall Barton

Orv said:
I have placed a command button on a form, that when clicked prompts for a
number, then should goto that record. Here is my code:

Command Button on click: =OpenFormWithInput()

Code: Function OpenFormWithInput()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter RTF Number (11111.)."
Title = "OPEN RTF FORM"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmRTF", , "[RTFNumber]='" & Answer & "' " []
When I open the form and click on the button, I enter a number (with or w/o
the hyphen) in the prompt and click OK, it then goes to the first record,
regardless of the nuber entered.
Click to expand...
[]

You're missing a comma:

DoCmd.OpenForm "frmRTF", , , "[R
 
O

Orv

OK, now when I enter a number and click OK, I get a Run Time error '2501' ,
"The OpenForm action was canceled." When I Debug, it highlights the same
line where I inserted the comma.
???





Marshall Barton said:
Orv said:
I have placed a command button on a form, that when clicked prompts for a
number, then should goto that record. Here is my code:

Command Button on click: =OpenFormWithInput()

Code: Function OpenFormWithInput()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter RTF Number (11111.)."
Title = "OPEN RTF FORM"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmRTF", , "[RTFNumber]='" & Answer & "' " []
When I open the form and click on the button, I enter a number (with or
w/o
the hyphen) in the prompt and click OK, it then goes to the first record,
regardless of the nuber entered.
Click to expand...
[]

You're missing a comma:

DoCmd.OpenForm "frmRTF", , , "[R
Click to expand...
 
T

Terry

Is RTFNumber really a number or is it a string?


Orv said:
OK, now when I enter a number and click OK, I get a Run Time error '2501'
, "The OpenForm action was canceled." When I Debug, it highlights the same
line where I inserted the comma.
???





Marshall Barton said:
Orv said:
I have placed a command button on a form, that when clicked prompts for a
number, then should goto that record. Here is my code:

Command Button on click: =OpenFormWithInput()

Code: Function OpenFormWithInput()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter RTF Number (11111.)."
Title = "OPEN RTF FORM"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmRTF", , "[RTFNumber]='" & Answer & "' " []
When I open the form and click on the button, I enter a number (with or
w/o
the hyphen) in the prompt and click OK, it then goes to the first record,
regardless of the nuber entered.
Click to expand...
[]

You're missing a comma:

DoCmd.OpenForm "frmRTF", , , "[R
Click to expand...
Click to expand...
 
T

Terry

Hit send to quick


You say hyphen and I'm thinking string then it would be
DoCmd.OpenForm "frmRTF", , , "[RTFNumber]=""" & Answer & """"

if number, ie not - but . then

DoCmd.OpenForm "frmRTF", , , "[RTFNumber]= "& Answer & ""




Orv said:
OK, now when I enter a number and click OK, I get a Run Time error '2501'
, "The OpenForm action was canceled." When I Debug, it highlights the same
line where I inserted the comma.
???





Marshall Barton said:
Orv said:
I have placed a command button on a form, that when clicked prompts for a
number, then should goto that record. Here is my code:

Command Button on click: =OpenFormWithInput()

Code: Function OpenFormWithInput()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter RTF Number (11111.)."
Title = "OPEN RTF FORM"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmRTF", , "[RTFNumber]='" & Answer & "' " []
When I open the form and click on the button, I enter a number (with or
w/o
the hyphen) in the prompt and click OK, it then goes to the first record,
regardless of the nuber entered.
Click to expand...
[]

You're missing a comma:

DoCmd.OpenForm "frmRTF", , , "[R
Click to expand...
Click to expand...
 
O

Orv

The second line of code you offered works. Thank you!

OK, now is it possible to put the same button on a different form and have
it open that form (frmRTF) to that record number that you entered when
prompted?
It works on the "frmRTF" fine. But when I put it on a different form, the
"frmRTF" pops up, but it just goes to the last record.


Terry said:
Hit send to quick


You say hyphen and I'm thinking string then it would be
DoCmd.OpenForm "frmRTF", , , "[RTFNumber]=""" & Answer & """"

if number, ie not - but . then

DoCmd.OpenForm "frmRTF", , , "[RTFNumber]= "& Answer & ""




Orv said:
OK, now when I enter a number and click OK, I get a Run Time error '2501'
, "The OpenForm action was canceled." When I Debug, it highlights the
same line where I inserted the comma.
???





Marshall Barton said:
Orv wrote:

I have placed a command button on a form, that when clicked prompts for
a
number, then should goto that record. Here is my code:

Command Button on click: =OpenFormWithInput()

Code:
Function OpenFormWithInput()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter RTF Number (11111.)."
Title = "OPEN RTF FORM"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmRTF", , "[RTFNumber]='" & Answer & "' "
[]
When I open the form and click on the button, I enter a number (with or
w/o
the hyphen) in the prompt and click OK, it then goes to the first
record,
regardless of the nuber entered.
[]

You're missing a comma:

DoCmd.OpenForm "frmRTF", , , "[R
Click to expand...
Click to expand...
Click to expand...
 
T

Terry

It should work fine from where ever you call the function.

You do have the function, OpenFormWithInput(), in a module and not behind a
form, right?

You are only testing Answer for an empty string. You may want to test for
the existance of the record in case an input error happens.

If there is no record found, I think it will show the last one.


Orv said:
The second line of code you offered works. Thank you!

OK, now is it possible to put the same button on a different form and have
it open that form (frmRTF) to that record number that you entered when
prompted?
It works on the "frmRTF" fine. But when I put it on a different form, the
"frmRTF" pops up, but it just goes to the last record.


Terry said:
Hit send to quick


You say hyphen and I'm thinking string then it would be
DoCmd.OpenForm "frmRTF", , , "[RTFNumber]=""" & Answer & """"

if number, ie not - but . then

DoCmd.OpenForm "frmRTF", , , "[RTFNumber]= "& Answer & ""




Orv said:
OK, now when I enter a number and click OK, I get a Run Time error
'2501' , "The OpenForm action was canceled." When I Debug, it highlights
the same line where I inserted the comma.
???





Orv wrote:

I have placed a command button on a form, that when clicked prompts for
a
number, then should goto that record. Here is my code:

Command Button on click: =OpenFormWithInput()

Code:
Function OpenFormWithInput()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter RTF Number (11111.)."
Title = "OPEN RTF FORM"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmRTF", , "[RTFNumber]='" & Answer & "' "
[]
When I open the form and click on the button, I enter a number (with or
w/o
the hyphen) in the prompt and click OK, it then goes to the first
record,
regardless of the nuber entered.
[]

You're missing a comma:

DoCmd.OpenForm "frmRTF", , , "[R
Click to expand...
Click to expand...
Click to expand...
 
O

Orv

You are correct..I was searching for a record that did not exist.

Thanks for all the help,

Orv


Terry said:
It should work fine from where ever you call the function.

You do have the function, OpenFormWithInput(), in a module and not behind
a form, right?

You are only testing Answer for an empty string. You may want to test for
the existance of the record in case an input error happens.

If there is no record found, I think it will show the last one.


Orv said:
The second line of code you offered works. Thank you!

OK, now is it possible to put the same button on a different form and
have it open that form (frmRTF) to that record number that you entered
when prompted?
It works on the "frmRTF" fine. But when I put it on a different form, the
"frmRTF" pops up, but it just goes to the last record.


Terry said:
Hit send to quick


You say hyphen and I'm thinking string then it would be
DoCmd.OpenForm "frmRTF", , , "[RTFNumber]=""" & Answer & """"

if number, ie not - but . then

DoCmd.OpenForm "frmRTF", , , "[RTFNumber]= "& Answer & ""




OK, now when I enter a number and click OK, I get a Run Time error
'2501' , "The OpenForm action was canceled." When I Debug, it
highlights the same line where I inserted the comma.
???





Orv wrote:

I have placed a command button on a form, that when clicked prompts
for a
number, then should goto that record. Here is my code:

Command Button on click: =OpenFormWithInput()

Code:
Function OpenFormWithInput()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter RTF Number (11111.)."
Title = "OPEN RTF FORM"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmRTF", , "[RTFNumber]='" & Answer & "' "
[]
When I open the form and click on the button, I enter a number (with
or w/o
the hyphen) in the prompt and click OK, it then goes to the first
record,
regardless of the nuber entered.
[]

You're missing a comma:

DoCmd.OpenForm "frmRTF", , , "[R
Click to expand...
Click to expand...
Click to expand...
 
O

Orv

I created a new module with the same code, changing the Function to:
OpenFormWithInput2(). I also changed the form name, and control name, etc..

Then I placed a second button on the same form and when I click on it, it
opens the correct form, but to the last record (same problem as before)???


1st Button (MCPO) works fine On Click =OpenFormWithInput1()

Code:

Option Explicit

Function OpenFormWithInput1()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter MCPO File Number (w/o hyphen i.e. 05000100)."
Title = "OPEN MCPO File & Suspect Information"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmMCPO", , , "[MCPONumber]= " & Answer & ""
Else
DoCmd.OpenForm "frmMCPO"
End If
End Function

2nd Button (RTF) On Click ==OpenFormWithInput2()

Code:

Option Explicit

Function OpenFormWithInput2()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter RTF Number (w/o hyphen i.e. 050001)."
Title = "OPEN RTF Referral"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmRTF", , , "[RTFNumber]= " & Answer & ""
Else
DoCmd.OpenForm "frmRTF"
End If
End Function
 
T

Terry

How many OpenFormWithInput functions are you going to need?
You can rewrite the function to accept form names, controlsources and fields
or just write a search form that generates a SQL statement. If you need
more that a couple, I would take another approach rather that writing
several functions.


Function OpenFormWithInput1()
Dim Msg As String
Dim Title As String
Dim Answer As String
Dim MCPONumber as long


Msg = "Enter MCPO File Number (w/o hyphen i.e. 05000100)."
Title = "OPEN MCPO File & Suspect Information"
Answer = InputBox(Msg, Title)
If Answer <> "" Then

'Test to see if there is a record

MCPONumber = Nz(DLookup("MCPONumber", "FormRecordSource",
"[MCPONumber] = " & Answer & ""), 0)
IF MCPONumber <> 0 Then
DoCmd.OpenForm "frmMCPO", , , "[MCPONumber]=" &
Answer & ""
Else
MsgBox "There is no record with that number!"

End If
Else
DoCmd.OpenForm "frmMCPO"
End If
End Function
 
O

Orv

Thanks for all your help. I figured it out. The 2nd form I was trying to
open with the module, I had placed a sort order for the on load, and when I
removed that the code worked fine.

Thanks again,
Orv



Terry said:
How many OpenFormWithInput functions are you going to need?
You can rewrite the function to accept form names, controlsources and
fields or just write a search form that generates a SQL statement. If you
need more that a couple, I would take another approach rather that
writing several functions.


Function OpenFormWithInput1()
Dim Msg As String
Dim Title As String
Dim Answer As String
Dim MCPONumber as long


Msg = "Enter MCPO File Number (w/o hyphen i.e. 05000100)."
Title = "OPEN MCPO File & Suspect Information"
Answer = InputBox(Msg, Title)
If Answer <> "" Then

'Test to see if there is a record

MCPONumber = Nz(DLookup("MCPONumber", "FormRecordSource",
"[MCPONumber] = " & Answer & ""), 0)
IF MCPONumber <> 0 Then
DoCmd.OpenForm "frmMCPO", , , "[MCPONumber]=" &
Answer & ""
Else
MsgBox "There is no record with that number!"

End If
Else
DoCmd.OpenForm "frmMCPO"
End If
End Function
Orv said:
I created a new module with the same code, changing the Function to:
OpenFormWithInput2(). I also changed the form name, and control name,
etc..

Then I placed a second button on the same form and when I click on it, it
opens the correct form, but to the last record (same problem as
before)???


1st Button (MCPO) works fine On Click =OpenFormWithInput1()

Code:

Option Explicit

Function OpenFormWithInput1()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter MCPO File Number (w/o hyphen i.e. 05000100)."
Title = "OPEN MCPO File & Suspect Information"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmMCPO", , , "[MCPONumber]= " & Answer & ""
Else
DoCmd.OpenForm "frmMCPO"
End If
End Function

2nd Button (RTF) On Click ==OpenFormWithInput2()

Code:

Option Explicit

Function OpenFormWithInput2()
Dim Msg As String
Dim Title As String
Dim Answer As String

Msg = "Enter RTF Number (w/o hyphen i.e. 050001)."
Title = "OPEN RTF Referral"
Answer = InputBox(Msg, Title)
If Answer <> "" Then
DoCmd.OpenForm "frmRTF", , , "[RTFNumber]= " & Answer & ""
Else
DoCmd.OpenForm "frmRTF"
End If
End Function
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Keep form open until loop completes 2
Access Command to run access, open to specific form and specific record 1
Finding a Specific record problem 2
Deleting a Form & SubForm Record 1
Open form to specific record from button on continuous form 4
Access Open Form with WhereCondition 6
fomr opens to wrong record 5
open form multiple criteria 3

Top