button that prompts for a value and then enters it into a field

K

kyle775

Anyone no where i might find some info on how to do this?
I want to click a button on my form, be prompted for a value (similar
to a parameter query prompt) and after i enter it and click "ok" it is
copied to a field.
I dont even know where to start.
 
K

kyle775

Thanks again for the advice. I've tried to do what i want using this
code
Private Sub cmdAddRga_Click()
RgaNumber = InputBox("Enter RGA Number", "Enter New RGA")
On Error GoTo Err_cmdAddRga_Click


DoCmd.GoToRecord , , acGoTo, RgaNumber

Exit_cmdAddRga_Click:
Exit Sub

Err_cmdAddRga_Click:
MsgBox Err.Description
Resume Exit_cmdAddRga_Click

End Sub

i get an error "Cannot goto specified record" and my form has the
values from what looks like the last record that was entered, with the
number i entered in the inputbox in that fields place.
Is there a way to enter the "RgaNumber" and then goto that record.?
 
C

Carl Rapson

The problem, GoToRecord takes you to a specific record number, which record
number is maintained by Access internally. It has nothing to do with your
own values (such as the RGA). There are various ways to do what you want,
but I like to use Bookmark:

Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "RGA=" & RgaNumber
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing

If you're using ADO instead of DAO, you'll need to do a little research to
find out how to do the same thing. Do a search on these newsgroups and I'm
sure you'll find plenty of examples.

Carl Rapson
 
K

kyle775

The problem, GoToRecord takes you to a specific record number, which record
number is maintained by Access internally. It has nothing to do with your
own values (such as the RGA). There are various ways to do what you want,
but I like to use Bookmark:

Dim rs as DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "RGA=" & RgaNumber
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
rs.Close
Set rs = Nothing

If you're using ADO instead of DAO, you'll need to do a little research to
find out how to do the same thing. Do a search on these newsgroups and I'm
sure you'll find plenty of examples.

Carl Rapson

I must admit i am not a seasoned VBA programmer. I still cannot get
this to work. The following is the result of reading a number of
posts, articles etc on what i thought was relavant information.
Please help?
Private Sub cmdAddRga_Click()
On Error GoTo Err_cmdAddRga_Click

Dim strSearchName As String
Dim rs As Object
Set rs = Me.Recordset.Clone
strSearchName = InputBox("Enter RGA Number", "Enter New RGA")

rs.FindFirst "[RgaNumber]=" & strSearchName
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

Exit_cmdAddRga_Click:
Exit Sub

Err_cmdAddRga_Click:
MsgBox Err.Description
Resume Exit_cmdAddRga_Click

End Sub

also can you please explain why DAO or ADO is necessary, many things i
read did not have this as a requirement for working with recordsets?
 
C

Carl Rapson

I must admit i am not a seasoned VBA programmer. I still cannot get
this to work. The following is the result of reading a number of
posts, articles etc on what i thought was relavant information.
Please help?
Private Sub cmdAddRga_Click()
On Error GoTo Err_cmdAddRga_Click

Dim strSearchName As String
Dim rs As Object
Set rs = Me.Recordset.Clone
strSearchName = InputBox("Enter RGA Number", "Enter New RGA")

rs.FindFirst "[RgaNumber]=" & strSearchName
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

Exit_cmdAddRga_Click:
Exit Sub

Err_cmdAddRga_Click:
MsgBox Err.Description
Resume Exit_cmdAddRga_Click

End Sub

also can you please explain why DAO or ADO is necessary, many things i
read did not have this as a requirement for working with recordsets?

Access uses either DAO or ADO to communicate with the underlying Jet
database (there are actually other interfaces as well, but DAO and ADO are
the most common). DAO used to be the default, but recent versions of Access
have switched to ADO as the default. That's what you're using in your code,
even if you don't realize it. You don't explain what you mean by 'cannot get
this to work'; are you getting an error or is it just not positioning you to
the record you want? If you're getting an error, you need to post details
about the error.

In either case, my guess is that Access may be confused about which
interface (ADO or DAO) you are using. Open any code window and go to
Tools -> References. You should have either a DAO library (for example,
Microsoft DAO 3.6 Object Library) or an ADO (for example, Microsoft ActiveX
Data Objects 2.8 Library) library checked. Since you apparently haven't done
anything with the references, I'm guessing that the ADO library is checked.
If neither are checked, you need to select one. If both are checked, you
need to either uncheck one of them, or be more specific in your code as to
which interface to use. Then do something like this:

Dim rs as DAO.Recordset ' If the DAO library is checked

or

Dim rs as ADO.Recordset ' If the ADO library is checked

The FindFirst method is only available in DAO recordsets, so I would suggest
that you go with DAO. Otherwise, you'll need to use the Find method of ADO
recordsets. You can look in the Help for more information about these
methods.

One last question: I don't know if this was asked before, but is RgaNumber
defined as a number or text in the table? If it's text, then even if you're
storing a number you'll need to put quotes around the value in the FindFirst
call:

rs.FindFirst "[RgaNumber]='" & strSearchName & "'"

Note that I've included single quotes inside the double quotes. This results
in a string that looks like this:

RgaNumber='1234'

Carl Rapson
 
K

kyle775

<snip>




I must admit i am not a seasoned VBA programmer. I still cannot get
this to work. The following is the result of reading a number of
posts, articles etc on what i thought was relavant information.
Please help?
Private Sub cmdAddRga_Click()
On Error GoTo Err_cmdAddRga_Click
Dim strSearchName As String
Dim rs As Object
Set rs = Me.Recordset.Clone
strSearchName = InputBox("Enter RGA Number", "Enter New RGA")
rs.FindFirst "[RgaNumber]=" & strSearchName
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
Exit_cmdAddRga_Click:
Exit Sub
Err_cmdAddRga_Click:
MsgBox Err.Description
Resume Exit_cmdAddRga_Click
also can you please explain why DAO or ADO is necessary, many things i
read did not have this as a requirement for working with recordsets?

Access uses either DAO or ADO to communicate with the underlying Jet
database (there are actually other interfaces as well, but DAO and ADO are
the most common). DAO used to be the default, but recent versions of Access
have switched to ADO as the default. That's what you're using in your code,
even if you don't realize it. You don't explain what you mean by 'cannot get
this to work'; are you getting an error or is it just not positioning you to
the record you want? If you're getting an error, you need to post details
about the error.

In either case, my guess is that Access may be confused about which
interface (ADO or DAO) you are using. Open any code window and go to
Tools -> References. You should have either a DAO library (for example,
Microsoft DAO 3.6 Object Library) or an ADO (for example, Microsoft ActiveX
Data Objects 2.8 Library) library checked. Since you apparently haven't done
anything with the references, I'm guessing that the ADO library is checked.
If neither are checked, you need to select one. If both are checked, you
need to either uncheck one of them, or be more specific in your code as to
which interface to use. Then do something like this:

Dim rs as DAO.Recordset ' If the DAO library is checked

or

Dim rs as ADO.Recordset ' If the ADO library is checked

The FindFirst method is only available in DAO recordsets, so I would suggest
that you go with DAO. Otherwise, you'll need to use the Find method of ADO
recordsets. You can look in the Help for more information about these
methods.

One last question: I don't know if this was asked before, but is RgaNumber
defined as a number or text in the table? If it's text, then even if you're
storing a number you'll need to put quotes around the value in the FindFirst
call:

rs.FindFirst "[RgaNumber]='" & strSearchName & "'"

Note that I've included single quotes inside the double quotes. This results
in a string that looks like this:

RgaNumber='1234'

Carl Rapson

Jeez thanks Carl, that was a long message, helpful too!
The RgaNumber is defined as a Number in my table.
ADO was enabled.
I am getting an error when compiling, "User-Defined type not defined"
with this line highlighted,
Dim rs As ADO.Recordset

Here is the complete code,

Private Sub cmdAddRga_Click()
On Error GoTo Err_cmdAddRga_Click

Dim strSearchName As String
Dim rs As ADO.Recordset
Set rs = Me.Recordset.Clone
strSearchName = InputBox("Enter RGA Number", "Enter New RGA")

rs.Find ("[RgaNumber]" = strSearchName)
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

Exit_cmdAddRga_Click:
Exit Sub

Err_cmdAddRga_Click:
MsgBox Err.Description
Resume Exit_cmdAddRga_Click

End Sub

Thanks for all your help so far, i am a bit in over my head now. i had
no idea how much this would require.
 
C

Carl Rapson

Jeez thanks Carl, that was a long message, helpful too!
The RgaNumber is defined as a Number in my table.
ADO was enabled.
I am getting an error when compiling, "User-Defined type not defined"
with this line highlighted,
Dim rs As ADO.Recordset

Here is the complete code,

Private Sub cmdAddRga_Click()
On Error GoTo Err_cmdAddRga_Click

Dim strSearchName As String
Dim rs As ADO.Recordset
Set rs = Me.Recordset.Clone
strSearchName = InputBox("Enter RGA Number", "Enter New RGA")

rs.Find ("[RgaNumber]" = strSearchName)
If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

Exit_cmdAddRga_Click:
Exit Sub

Err_cmdAddRga_Click:
MsgBox Err.Description
Resume Exit_cmdAddRga_Click

End Sub

Thanks for all your help so far, i am a bit in over my head now. i had
no idea how much this would require.

Sorry, that was my fault. The library is called ADO, but in code you refer
to it as ADODB:

Dim rs as ADODB.Recordset

Give that a try. Be sure to use Help to verify the sytax of the Find method,
too. I'm more familiar with DAO than ADO, so I may not be able to help you
much in debugging the Find method call (if you need it).

Carl Rapson
 

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

Top