Proper use of Parameter

[Guest]

Thanks for taking the time to read my question.

I have code that opens a recordset. The recordset is a query that has 2
criteria. I know that I need to have qdf.parameter(0) in there, but not sure
how to use it.

Am I on the right track?

Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef



Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrySpecDates")
Set qdf.Parameters(0) =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![tblFarmDataIDNumber]
Set qdf.Parameters(1) =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![SpecCategory]


Set rst = qdf.OpenRecordset()

rst.MoveFirst
MsgBox rst!SpecCategory


rst.Close
Set dbs = Nothing

Thanks,

Brad

 

[OfficeDev18 via AccessMonster.com]

Hi, Brad,

You Set an object; a parameter is not an object that needs Setting, so you
only say

qdf.Parameters(0) = [Forms]![frmtblFarmData]![frmtblSpec].[Form]!
[tblFarmDataIDNumber]
qdf.Parameters(1) = [Forms]![frmtblFarmData]![frmtblSpec].[Form]!
[SpecCategory]

Also, look up With...End With in the help file. It's a helpful shortcut.

Hope this helps,

Sam

P.S. I'm not sure of your usage of [Forms]![frmtblFarmData]![frmtblSpec].
[Form]![tblFarmDataIDNumber]
and [Forms]![frmtblFarmData]![frmtblSpec].[Form]![SpecCategory] multiple form
layers? I'm not familiar with this format.

Thanks for taking the time to read my question.

I have code that opens a recordset. The recordset is a query that has 2
criteria. I know that I need to have qdf.parameter(0) in there, but not sure
how to use it.

Am I on the right track?

Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef



Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrySpecDates")
Set qdf.Parameters(0) =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![tblFarmDataIDNumber]
Set qdf.Parameters(1) =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![SpecCategory]


Set rst = qdf.OpenRecordset()

rst.MoveFirst
MsgBox rst!SpecCategory


rst.Close
Set dbs = Nothing

Thanks,

Brad

 

[Guest]

Hi, and thanks for your reply.

The form reference is what the builder gave me. If I'm correct using the
"!" is an implicit reference. Access seems to like this better.

Here is my final code that worked.

On Error GoTo Form_AfterUpdate_Err

If SpecDateChange = True Then
Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim UseDate, UseDate2 As Date


Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrySpecDates")
qdf.Parameters(0).Value =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![tblFarmDataIDNumber]
qdf.Parameters(1).Value =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![SpecCategory]


Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
rst.MoveFirst
If rst.RecordCount > 1 Then
Do Until rst.EOF
UseDate = rst!SpecDate
rst.MoveNext
UseDate2 = rst!SpecDate
With rst
.Edit
!SpecEndDate = UseDate - 1
.Update
End With
Loop
Else
MsgBox "No data to update"
qdf.Close
rst.Close
Set dbs = Nothing
Exit Sub
End If

qdf.Close
rst.Close
Set dbs = Nothing
End If
End If

Form_AfterUpdate_Exit:
Exit Sub

Form_AfterUpdate_Err:
If Err.Number = 3021 Then
qdf.Close
rst.Close
Set dbs = Nothing
Resume Form_AfterUpdate_Exit
Else
MsgBox Err.Number & ", " & Err.Description
Resume Form_AfterUpdate_Exit
End If

Brad

Hi, Brad,

You Set an object; a parameter is not an object that needs Setting, so you
only say

qdf.Parameters(0) = [Forms]![frmtblFarmData]![frmtblSpec].[Form]!
[tblFarmDataIDNumber]
qdf.Parameters(1) = [Forms]![frmtblFarmData]![frmtblSpec].[Form]!
[SpecCategory]

Also, look up With...End With in the help file. It's a helpful shortcut.

Hope this helps,

Sam

P.S. I'm not sure of your usage of [Forms]![frmtblFarmData]![frmtblSpec].
[Form]![tblFarmDataIDNumber]
and [Forms]![frmtblFarmData]![frmtblSpec].[Form]![SpecCategory] multiple form
layers? I'm not familiar with this format.

Thanks for taking the time to read my question.

I have code that opens a recordset. The recordset is a query that has 2
criteria. I know that I need to have qdf.parameter(0) in there, but not sure
how to use it.

Am I on the right track?

Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef



Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrySpecDates")
Set qdf.Parameters(0) =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![tblFarmDataIDNumber]
Set qdf.Parameters(1) =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![SpecCategory]


Set rst = qdf.OpenRecordset()

rst.MoveFirst
MsgBox rst!SpecCategory


rst.Close
Set dbs = Nothing

Thanks,

Brad

 

[Guest]

Your parameter usage appears to be correct; however, how you are addressing
the controls on your form is unusual. I am guessing you are setting this in
the Main form based on data in a sub form. The syntax you want is:
Forms!MainFomrName!SubFormName.ControlName

[Forms]![frmtblFarmData]![frmtblSpec].[Form]![tblFarmDataIDNumber]

I am suspicious about the line above because of the naming. In usual naming
conventions, anything prefixed with tbl would be a table name, not a control
name. Also, your sub form should be prefixed with fsub rather than frm.
That is not an issue for Access, but it helps us humans understand the
relationship of the objects a little better. Try this syntax:

Forms!frmtblFarmData!frmtblSpec!tblFarmDataIDNumber

The brackets are okay, but my presonal preference is to use them only when
the name could be confused by Access. Like when some moron ahead of me named
a control or table field something like Date or Name.

 

[Guest]

Depending on the size of your recordset, you could have a problem here. The
problem is the RecordCount property will not be correct until the recordset
is fully populated. To be sure it is fully populated before you start
processing, use the MoveLast method. I also don't trust the EOF property at
this point. Below is a modification that ensures you wont have an error
because of a recordset that is not fully populated:

If rst.RecordCount > 0 Then
rst.MoveLast
rst.MoveFirst
Do Until rst.EOF

Hi, and thanks for your reply.

The form reference is what the builder gave me. If I'm correct using the
"!" is an implicit reference. Access seems to like this better.

Here is my final code that worked.

On Error GoTo Form_AfterUpdate_Err

If SpecDateChange = True Then
Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim UseDate, UseDate2 As Date


Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrySpecDates")
qdf.Parameters(0).Value =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![tblFarmDataIDNumber]
qdf.Parameters(1).Value =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![SpecCategory]


Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
rst.MoveFirst
If rst.RecordCount > 1 Then
Do Until rst.EOF
UseDate = rst!SpecDate
rst.MoveNext
UseDate2 = rst!SpecDate
With rst
.Edit
!SpecEndDate = UseDate - 1
.Update
End With
Loop
Else
MsgBox "No data to update"
qdf.Close
rst.Close
Set dbs = Nothing
Exit Sub
End If

qdf.Close
rst.Close
Set dbs = Nothing
End If
End If

Form_AfterUpdate_Exit:
Exit Sub

Form_AfterUpdate_Err:
If Err.Number = 3021 Then
qdf.Close
rst.Close
Set dbs = Nothing
Resume Form_AfterUpdate_Exit
Else
MsgBox Err.Number & ", " & Err.Description
Resume Form_AfterUpdate_Exit
End If

Brad

Hi, Brad,

You Set an object; a parameter is not an object that needs Setting, so you
only say

qdf.Parameters(0) = [Forms]![frmtblFarmData]![frmtblSpec].[Form]!
[tblFarmDataIDNumber]
qdf.Parameters(1) = [Forms]![frmtblFarmData]![frmtblSpec].[Form]!
[SpecCategory]

Also, look up With...End With in the help file. It's a helpful shortcut.

Hope this helps,

Sam

P.S. I'm not sure of your usage of [Forms]![frmtblFarmData]![frmtblSpec].
[Form]![tblFarmDataIDNumber]
and [Forms]![frmtblFarmData]![frmtblSpec].[Form]![SpecCategory] multiple form
layers? I'm not familiar with this format.

Thanks for taking the time to read my question.

I have code that opens a recordset. The recordset is a query that has 2
criteria. I know that I need to have qdf.parameter(0) in there, but not sure
how to use it.

Am I on the right track?

Dim dbs As Database
Dim rst As Recordset
Dim qdf As QueryDef



Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qrySpecDates")
Set qdf.Parameters(0) =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![tblFarmDataIDNumber]
Set qdf.Parameters(1) =
[Forms]![frmtblFarmData]![frmtblSpec].[Form]![SpecCategory]


Set rst = qdf.OpenRecordset()

rst.MoveFirst
MsgBox rst!SpecCategory


rst.Close
Set dbs = Nothing

Thanks,

Brad