Revision to form VBA Error

  • Thread starter misschanda via AccessMonster.com
  • Start date
M

misschanda via AccessMonster.com

have a form, where if needed a change button is pushed and a new form opens
listing all the records. The list box allows the user to scroll and select
the record in which he/she wants to revise. upon selection the chosen record
should open and changes can be made, And than it is added into the main list
of records.

I am getting the following error:

Set rs1 = db1.OpenRecordset(QueryTxt, dbOpenDynaset)

The VB code I am using starts off as following:

Private Sub Command4_Click()
Dim QueryTxt, QueryParm, TrialNum, LineNum As String
Dim RevNum, RevNumGrid, RevNumToRetreive As Integer
Dim rs1 As Recordset
Dim db1 As Database
Set db1 = CurrentDb

TrialNum = List10.Column(3, List10.ListIndex + 1)
RevNumGrid = List10.Column(5, List10.ListIndex + 1)

'The selected Option Button value is stored in the frame... How dumb is
that?
Select Case Frame8

Case 1
'Create a new version of the selected Guideline...
'First retrieve the highest revision number...
If List10.Column(3, List10.ListIndex + 1) = "Trial_Number" Then
MsgBox "You must select a Lab Line record to copy to a new
revision."
Exit Sub
End If

QueryTxt = "SELECT Max([Revision]) AS Expr1 FROM
[Lab_LIne_Processing_Record2] WHERE [Lab_LIne_Processing-Record2].
Trial_Number='" + List10.Column(3, List10.ListIndex + 1) + "' AND
[Lab_LIne_Processing_Record2].Project_Number='" + List10.Column(4, List10.
ListIndex + 1) + "';"
Set rs1 = db1.OpenRecordset(QueryTxt, dbOpenDynaset)

RevNum = rs1!Expr1 + 1
 
R

Roger Carlson

This is just an aside, but these two lines:
Dim QueryTxt, QueryParm, TrialNum, LineNum As String
Dim RevNum, RevNumGrid, RevNumToRetreive As Integer

don't do what you think they do. In both lines, ONLY the last variable is
typed (String and Integer, respectively). The others are declared as
Variants. You HAVE to use the As Type after each variable.

Dim QueryTxt As String, QueryParm As String, TrialNum As String, LineNum
As String
Dim RevNum As Integer, RevNumGrid As Integer, RevNumToRetreive As
Integer

It's actually better to have each on a separate line, though

Dim QueryTxt As String
Dim QueryParm As String
Dim TrialNum As String
Dim LineNum As String
Dim RevNum As Integer
Dim RevNum As Integer
Dim RevNum As Integer
 
R

Roger Carlson

This got sent prematurely. To finish:

It's actually better to have each on a separate line, though

Dim QueryTxt As String
Dim QueryParm As String
Dim TrialNum As String
Dim LineNum As String
Dim RevNum As Integer
Dim RevNumGrid As Integer
Dim RevNumToRetreive As Integer

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


Roger Carlson said:
This is just an aside, but these two lines:
Dim QueryTxt, QueryParm, TrialNum, LineNum As String
Dim RevNum, RevNumGrid, RevNumToRetreive As Integer

don't do what you think they do. In both lines, ONLY the last variable is
typed (String and Integer, respectively). The others are declared as
Variants. You HAVE to use the As Type after each variable.

Dim QueryTxt As String, QueryParm As String, TrialNum As String,
LineNum As String
Dim RevNum As Integer, RevNumGrid As Integer, RevNumToRetreive As
Integer

It's actually better to have each on a separate line, though

Dim QueryTxt As String
Dim QueryParm As String
Dim TrialNum As String
Dim LineNum As String
Dim RevNum As Integer
Dim RevNum As Integer
Dim RevNum As Integer


misschanda via AccessMonster.com said:
have a form, where if needed a change button is pushed and a new form
opens
listing all the records. The list box allows the user to scroll and
select
the record in which he/she wants to revise. upon selection the chosen
record
should open and changes can be made, And than it is added into the main
list
of records.

I am getting the following error:

Set rs1 = db1.OpenRecordset(QueryTxt, dbOpenDynaset)

The VB code I am using starts off as following:

Private Sub Command4_Click()
Dim QueryTxt, QueryParm, TrialNum, LineNum As String
Dim RevNum, RevNumGrid, RevNumToRetreive As Integer
Dim rs1 As Recordset
Dim db1 As Database
Set db1 = CurrentDb

TrialNum = List10.Column(3, List10.ListIndex + 1)
RevNumGrid = List10.Column(5, List10.ListIndex + 1)

'The selected Option Button value is stored in the frame... How dumb
is
that?
Select Case Frame8

Case 1
'Create a new version of the selected Guideline...
'First retrieve the highest revision number...
If List10.Column(3, List10.ListIndex + 1) = "Trial_Number" Then
MsgBox "You must select a Lab Line record to copy to a new
revision."
Exit Sub
End If

QueryTxt = "SELECT Max([Revision]) AS Expr1 FROM
[Lab_LIne_Processing_Record2] WHERE [Lab_LIne_Processing-Record2].
Trial_Number='" + List10.Column(3, List10.ListIndex + 1) + "' AND
[Lab_LIne_Processing_Record2].Project_Number='" + List10.Column(4,
List10.
ListIndex + 1) + "';"
Set rs1 = db1.OpenRecordset(QueryTxt, dbOpenDynaset)

RevNum = rs1!Expr1 + 1
Click to expand...
Click to expand...
 
N

NKTower

It's hard to see what the error message is - is it misplaced within the text
of your code? However I do see one thing that could be causing a problem.
In VBA when you declare a variable you must do them one at a time. It isn't
like other languages such as C where you list several variables of the same
type.

For the example: Dim A, B, C as String
C will be a string as intended, A and B will be Variants - the default if
you just DIM something. Generally you want strong typing, so put the
declarations one item per line.

So if the lne being rejected is:
Set rs1 = db1.OpenRecordset(QueryTxt, dbOpenDynaset)
Then it might be because QueryTxt is type Variant rather than String, and
the OpenRecordset method can't determine what to do with it. Declare
QueryTxt to be a string and see what happens.
 
M

misschanda via AccessMonster.com

Two more errors appeared after corrections made, beging w/ list10

Private Sub Form_Load()
' MsgBox (Option0.Value)
Frame8.Value = 1
List10.RowSource = "SELECT [Lab_LIne_Processing_Record2].* FROM
[Lab_LIne_Processing_Record2] WHERE ((([Lab_LIne_Processing_Record2].
Trial_Number)='" + Forms![Lab_Line_Processing_Record_Sheet].Trial_Number.
Value + "'));"
Roger Carlson wrote:

And

Here: Type Mismatch begining w/ RevNumGrid.

Private Sub Command4_Click()
Dim QueryTxt As String
Dim QueryParm As String
Dim TrialNum As String
Dim LineNum As String
Dim RevNum As Integer
Dim RevNumGrid As Integer
Dim RevNumToRetreive As Integer
Dim rs1 As DAO.Recordset
Dim db1 As DAO.Database
Set db1 = CurrentDb

TrialNum = List10.Column(3, List10.ListIndex + 1)
RevNumGrid = List10.Column(5, List10.ListIndex + 1)
Try changing these two lines:
Dim rs1 As Recordset
Dim db1 As Database

to this:

Dim rs1 As DAO.Recordset
Dim db1 As DAO.Database
have a form, where if needed a change button is pushed and a new form
opens
Click to expand...
[quoted text clipped - 42 lines]
RevNum = rs1!Expr1 + 1
Click to expand...
Click to expand...
 
M

misschanda via AccessMonster.com

Two more errors appeared after corrections made, beging w/ list10

Private Sub Form_Load()
' MsgBox (Option0.Value)
Frame8.Value = 1
List10.RowSource = "SELECT [Lab_LIne_Processing_Record2].* FROM
[Lab_LIne_Processing_Record2] WHERE ((([Lab_LIne_Processing_Record2].
Trial_Number)='" + Forms![Lab_Line_Processing_Record_Sheet].Trial_Number.
Value + "'));"
Roger Carlson wrote:

And

Here: Type Mismatch begining w/ RevNumGrid.

Private Sub Command4_Click()
Dim QueryTxt As String
Dim QueryParm As String
Dim TrialNum As String
Dim LineNum As String
Dim RevNum As Integer
Dim RevNumGrid As Integer
Dim RevNumToRetreive As Integer
Dim rs1 As DAO.Recordset
Dim db1 As DAO.Database
Set db1 = CurrentDb

TrialNum = List10.Column(3, List10.ListIndex + 1)
RevNumGrid = List10.Column(5, List10.ListIndex + 1)
It's hard to see what the error message is - is it misplaced within the text
of your code? However I do see one thing that could be causing a problem.
In VBA when you declare a variable you must do them one at a time. It isn't
like other languages such as C where you list several variables of the same
type.

For the example: Dim A, B, C as String
C will be a string as intended, A and B will be Variants - the default if
you just DIM something. Generally you want strong typing, so put the
declarations one item per line.

So if the lne being rejected is:
Set rs1 = db1.OpenRecordset(QueryTxt, dbOpenDynaset)
Then it might be because QueryTxt is type Variant rather than String, and
the OpenRecordset method can't determine what to do with it. Declare
QueryTxt to be a string and see what happens.
have a form, where if needed a change button is pushed and a new form opens
listing all the records. The list box allows the user to scroll and select
Click to expand...
[quoted text clipped - 39 lines]
RevNum = rs1!Expr1 + 1
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

List/Form/SQL Help 1
Help with Code 1
populate listbox form database table 0
BOM Question 0
Run time error in VBA Code 1
comparing the date entered to a form and the date field in a table 2
Populate Userform Listbox with Access values 5
Drop Down doesn't populate form 0

Top