Error 2465 getting recordset for subform

  • Thread starter Thread starter D
  • Start date Start date
D

D

Hi,

Although I have read the threads about getting data into a subform, I am
still getting this error. I am fairly new on access development and have not
used subforms yet.

Here is what I am trying to do:

1. On main form Timesheet_Entry, users enter an account number and a
contract number. Then press Get Details.

2. ONce pressed, subform Contractor_Rates_Subform will display the rates
associated with the contract and the contractor. I have a predefined qry that
has the main requirements for returned fields, the user entered fields will
adjust this query to display the details just for that contractor.

3. THe code I am using is as follows:

From main form:

Call Set_Rates(" WHERE (((CONTRACT.NUMBER)='" & contract_number & "') AND
((CONTRACTOR.ACCOUNT)='" & contractor_account & "'))")


Function Set_Rates(sql1 As String)
Dim original_sql As String
replace_sql = sql1

'modify the qry to select contractor rate details'
original_sql = qdf.SQL
sqlstring = original_sql
sqlstring = Replace(sqlstring, ";", replace_sql)
qdf.SQL = sqlstring
MsgBox (qdf.SQL)

' update the recordset for the subform'

'Form![Contractor_Rates_subform].Form.Visible = True'
'Me.Contractor_Rates_subform.SourceObject = qdf.SQL'
Me!Contractor_Rates_subform.Form.Recordset = qdf.SQL

'restore original query
qdf.SQL = original_sql

qdf.Close

End Function

I have clicked once on the control for the subform on the main form and it
appears the name of the control is Contractor_Rates_subform

but I get the error 2465 when I step through the code to the
Me!Contractor_Rates_subform.Form.Recordset = qdf.SQL.

Any advice

D
 
I think you are making it harder than it is. One thing I don't understand is
what you mean by predefined fields as opposed to user entered fields. Do you
mean the account and contract number the user enters?

The error you are getting indicates there is a field that can't be found,
but you don't say where the error occurs.

I think all you really need to do is is refer to the text boxes on the main
form where you enter the values in the subform's query. For example, in the
criteria for the Number field, you would refer to the contract_number control
on your form:

Form!FormName!contract_number

And a similar expression for the Account.

Then all that is necessary is when you click the Get Details button is to
requery the subform:

Me!MySubformControlName.Form.Requery
 
Hi,

Thanks for the help. I was missing the contractor.account field from the
query. Once I added this the error message 2465 disappeared but I am still
getting all the rates details that are entered on the rates table instead of
just the rates for the contract and contractor numbers that the user enters.

I tried using the requery method but still gives all the rates and not just
for the contractor/contract.

Using either Me.subform_rates.SourceObject = qdf.SQL' or
' Form!subform_rates.SourceObject = qdf.SQL'

I get error message 2124 The form name you entered doesn't follow Microsoft
Access object-naming rules. To ensure I am getting the error from this line
I have stepped through the code using a breakpoint just before these lines.

If I can get this working, I can move to the next stage where the user
enters new timesheets.

Sorry if this is long winded .

d

Klatuu said:
I think you are making it harder than it is. One thing I don't understand is
what you mean by predefined fields as opposed to user entered fields. Do you
mean the account and contract number the user enters?

The error you are getting indicates there is a field that can't be found,
but you don't say where the error occurs.

I think all you really need to do is is refer to the text boxes on the main
form where you enter the values in the subform's query. For example, in the
criteria for the Number field, you would refer to the contract_number control
on your form:

Form!FormName!contract_number

And a similar expression for the Account.

Then all that is necessary is when you click the Get Details button is to
requery the subform:

Me!MySubformControlName.Form.Requery
--
Dave Hargis, Microsoft Access MVP


D said:
Hi,

Although I have read the threads about getting data into a subform, I am
still getting this error. I am fairly new on access development and have not
used subforms yet.

Here is what I am trying to do:

1. On main form Timesheet_Entry, users enter an account number and a
contract number. Then press Get Details.

2. ONce pressed, subform Contractor_Rates_Subform will display the rates
associated with the contract and the contractor. I have a predefined qry that
has the main requirements for returned fields, the user entered fields will
adjust this query to display the details just for that contractor.

3. THe code I am using is as follows:

From main form:

Call Set_Rates(" WHERE (((CONTRACT.NUMBER)='" & contract_number & "') AND
((CONTRACTOR.ACCOUNT)='" & contractor_account & "'))")


Function Set_Rates(sql1 As String)
Dim original_sql As String
replace_sql = sql1

'modify the qry to select contractor rate details'
original_sql = qdf.SQL
sqlstring = original_sql
sqlstring = Replace(sqlstring, ";", replace_sql)
qdf.SQL = sqlstring
MsgBox (qdf.SQL)

' update the recordset for the subform'

'Form![Contractor_Rates_subform].Form.Visible = True'
'Me.Contractor_Rates_subform.SourceObject = qdf.SQL'
Me!Contractor_Rates_subform.Form.Recordset = qdf.SQL

'restore original query
qdf.SQL = original_sql

qdf.Close

End Function

I have clicked once on the control for the subform on the main form and it
appears the name of the control is Contractor_Rates_subform

but I get the error 2465 when I step through the code to the
Me!Contractor_Rates_subform.Form.Recordset = qdf.SQL.

Any advice

D
Click to expand...
Click to expand...
 
The problem is how you are referencing the objects.
Me.subform_rates.SourceObject = qdf.SQL'

First, the Source Object of a subform control is not a query. It is the
name of a form that will be used as the subform.
And, you do not directly address the name of the form identified in the
subform control's Source Object property. You refer to it using the word
form. The correct syntax if you are assigning a record source to the subform
would be:
Me.SubformControlName.Form.Recordsource

Now, as I said before, you do not need to do this. You need to set the
filtering correctly in the query that is the subform's record source. The
requery will then return only the records for the account and contract
numbers you specifiy in the text boxes.
--
Dave Hargis, Microsoft Access MVP


D said:
Hi,

Thanks for the help. I was missing the contractor.account field from the
query. Once I added this the error message 2465 disappeared but I am still
getting all the rates details that are entered on the rates table instead of
just the rates for the contract and contractor numbers that the user enters.

I tried using the requery method but still gives all the rates and not just
for the contractor/contract.

Using either Me.subform_rates.SourceObject = qdf.SQL' or
' Form!subform_rates.SourceObject = qdf.SQL'

I get error message 2124 The form name you entered doesn't follow Microsoft
Access object-naming rules. To ensure I am getting the error from this line
I have stepped through the code using a breakpoint just before these lines.

If I can get this working, I can move to the next stage where the user
enters new timesheets.

Sorry if this is long winded .

d

Klatuu said:
I think you are making it harder than it is. One thing I don't understand is
what you mean by predefined fields as opposed to user entered fields. Do you
mean the account and contract number the user enters?

The error you are getting indicates there is a field that can't be found,
but you don't say where the error occurs.

I think all you really need to do is is refer to the text boxes on the main
form where you enter the values in the subform's query. For example, in the
criteria for the Number field, you would refer to the contract_number control
on your form:

Form!FormName!contract_number

And a similar expression for the Account.

Then all that is necessary is when you click the Get Details button is to
requery the subform:

Me!MySubformControlName.Form.Requery
--
Dave Hargis, Microsoft Access MVP


D said:
Hi,

Although I have read the threads about getting data into a subform, I am
still getting this error. I am fairly new on access development and have not
used subforms yet.

Here is what I am trying to do:

1. On main form Timesheet_Entry, users enter an account number and a
contract number. Then press Get Details.

2. ONce pressed, subform Contractor_Rates_Subform will display the rates
associated with the contract and the contractor. I have a predefined qry that
has the main requirements for returned fields, the user entered fields will
adjust this query to display the details just for that contractor.

3. THe code I am using is as follows:

From main form:

Call Set_Rates(" WHERE (((CONTRACT.NUMBER)='" & contract_number & "') AND
((CONTRACTOR.ACCOUNT)='" & contractor_account & "'))")


Function Set_Rates(sql1 As String)
Dim original_sql As String
replace_sql = sql1

'modify the qry to select contractor rate details'
original_sql = qdf.SQL
sqlstring = original_sql
sqlstring = Replace(sqlstring, ";", replace_sql)
qdf.SQL = sqlstring
MsgBox (qdf.SQL)

' update the recordset for the subform'

'Form![Contractor_Rates_subform].Form.Visible = True'
'Me.Contractor_Rates_subform.SourceObject = qdf.SQL'
Me!Contractor_Rates_subform.Form.Recordset = qdf.SQL

'restore original query
qdf.SQL = original_sql

qdf.Close

End Function

I have clicked once on the control for the subform on the main form and it
appears the name of the control is Contractor_Rates_subform

but I get the error 2465 when I step through the code to the
Me!Contractor_Rates_subform.Form.Recordset = qdf.SQL.

Any advice

D
Click to expand...
Click to expand...
Click to expand...
 
Hi,

Thanks for the help.

I have amended the SourceObject on the subform_rates control to use a form
called Contractor_Rates_subform. Then I set
Me.SubformControlName.Form.Recordsource = qdf.sql and I get exactly the
entries I need.

Thanks.
d

Klatuu said:
The problem is how you are referencing the objects.
Me.subform_rates.SourceObject = qdf.SQL'

First, the Source Object of a subform control is not a query. It is the
name of a form that will be used as the subform.
And, you do not directly address the name of the form identified in the
subform control's Source Object property. You refer to it using the word
form. The correct syntax if you are assigning a record source to the subform
would be:
Me.SubformControlName.Form.Recordsource

Now, as I said before, you do not need to do this. You need to set the
filtering correctly in the query that is the subform's record source. The
requery will then return only the records for the account and contract
numbers you specifiy in the text boxes.
--
Dave Hargis, Microsoft Access MVP


D said:
Hi,

Thanks for the help. I was missing the contractor.account field from the
query. Once I added this the error message 2465 disappeared but I am still
getting all the rates details that are entered on the rates table instead of
just the rates for the contract and contractor numbers that the user enters.

I tried using the requery method but still gives all the rates and not just
for the contractor/contract.

Using either Me.subform_rates.SourceObject = qdf.SQL' or
' Form!subform_rates.SourceObject = qdf.SQL'

I get error message 2124 The form name you entered doesn't follow Microsoft
Access object-naming rules. To ensure I am getting the error from this line
I have stepped through the code using a breakpoint just before these lines.

If I can get this working, I can move to the next stage where the user
enters new timesheets.

Sorry if this is long winded .

d

Klatuu said:
I think you are making it harder than it is. One thing I don't understand is
what you mean by predefined fields as opposed to user entered fields. Do you
mean the account and contract number the user enters?

The error you are getting indicates there is a field that can't be found,
but you don't say where the error occurs.

I think all you really need to do is is refer to the text boxes on the main
form where you enter the values in the subform's query. For example, in the
criteria for the Number field, you would refer to the contract_number control
on your form:

Form!FormName!contract_number

And a similar expression for the Account.

Then all that is necessary is when you click the Get Details button is to
requery the subform:

Me!MySubformControlName.Form.Requery
--
Dave Hargis, Microsoft Access MVP


:

Hi,

Although I have read the threads about getting data into a subform, I am
still getting this error. I am fairly new on access development and have not
used subforms yet.

Here is what I am trying to do:

1. On main form Timesheet_Entry, users enter an account number and a
contract number. Then press Get Details.

2. ONce pressed, subform Contractor_Rates_Subform will display the rates
associated with the contract and the contractor. I have a predefined qry that
has the main requirements for returned fields, the user entered fields will
adjust this query to display the details just for that contractor.

3. THe code I am using is as follows:

From main form:

Call Set_Rates(" WHERE (((CONTRACT.NUMBER)='" & contract_number & "') AND
((CONTRACTOR.ACCOUNT)='" & contractor_account & "'))")


Function Set_Rates(sql1 As String)
Dim original_sql As String
replace_sql = sql1

'modify the qry to select contractor rate details'
original_sql = qdf.SQL
sqlstring = original_sql
sqlstring = Replace(sqlstring, ";", replace_sql)
qdf.SQL = sqlstring
MsgBox (qdf.SQL)

' update the recordset for the subform'

'Form![Contractor_Rates_subform].Form.Visible = True'
'Me.Contractor_Rates_subform.SourceObject = qdf.SQL'
Me!Contractor_Rates_subform.Form.Recordset = qdf.SQL

'restore original query
qdf.SQL = original_sql

qdf.Close

End Function

I have clicked once on the control for the subform on the main form and it
appears the name of the control is Contractor_Rates_subform

but I get the error 2465 when I step through the code to the
Me!Contractor_Rates_subform.Form.Recordset = qdf.SQL.

Any advice

D
Click to expand...
Click to expand...
Click to expand...
 
Glad I could help.
Please make the question as answered so others with similar questions can
refer to it.
--
Dave Hargis, Microsoft Access MVP


D said:
Hi,

Thanks for the help.

I have amended the SourceObject on the subform_rates control to use a form
called Contractor_Rates_subform. Then I set
Me.SubformControlName.Form.Recordsource = qdf.sql and I get exactly the
entries I need.

Thanks.
d

Klatuu said:
The problem is how you are referencing the objects.
Me.subform_rates.SourceObject = qdf.SQL'

First, the Source Object of a subform control is not a query. It is the
name of a form that will be used as the subform.
And, you do not directly address the name of the form identified in the
subform control's Source Object property. You refer to it using the word
form. The correct syntax if you are assigning a record source to the subform
would be:
Me.SubformControlName.Form.Recordsource

Now, as I said before, you do not need to do this. You need to set the
filtering correctly in the query that is the subform's record source. The
requery will then return only the records for the account and contract
numbers you specifiy in the text boxes.
--
Dave Hargis, Microsoft Access MVP


D said:
Hi,

Thanks for the help. I was missing the contractor.account field from the
query. Once I added this the error message 2465 disappeared but I am still
getting all the rates details that are entered on the rates table instead of
just the rates for the contract and contractor numbers that the user enters.

I tried using the requery method but still gives all the rates and not just
for the contractor/contract.

Using either Me.subform_rates.SourceObject = qdf.SQL' or
' Form!subform_rates.SourceObject = qdf.SQL'

I get error message 2124 The form name you entered doesn't follow Microsoft
Access object-naming rules. To ensure I am getting the error from this line
I have stepped through the code using a breakpoint just before these lines.

If I can get this working, I can move to the next stage where the user
enters new timesheets.

Sorry if this is long winded .

d

:

I think you are making it harder than it is. One thing I don't understand is
what you mean by predefined fields as opposed to user entered fields. Do you
mean the account and contract number the user enters?

The error you are getting indicates there is a field that can't be found,
but you don't say where the error occurs.

I think all you really need to do is is refer to the text boxes on the main
form where you enter the values in the subform's query. For example, in the
criteria for the Number field, you would refer to the contract_number control
on your form:

Form!FormName!contract_number

And a similar expression for the Account.

Then all that is necessary is when you click the Get Details button is to
requery the subform:

Me!MySubformControlName.Form.Requery
--
Dave Hargis, Microsoft Access MVP


:

Hi,

Although I have read the threads about getting data into a subform, I am
still getting this error. I am fairly new on access development and have not
used subforms yet.

Here is what I am trying to do:

1. On main form Timesheet_Entry, users enter an account number and a
contract number. Then press Get Details.

2. ONce pressed, subform Contractor_Rates_Subform will display the rates
associated with the contract and the contractor. I have a predefined qry that
has the main requirements for returned fields, the user entered fields will
adjust this query to display the details just for that contractor.

3. THe code I am using is as follows:

From main form:

Call Set_Rates(" WHERE (((CONTRACT.NUMBER)='" & contract_number & "') AND
((CONTRACTOR.ACCOUNT)='" & contractor_account & "'))")


Function Set_Rates(sql1 As String)
Dim original_sql As String
replace_sql = sql1

'modify the qry to select contractor rate details'
original_sql = qdf.SQL
sqlstring = original_sql
sqlstring = Replace(sqlstring, ";", replace_sql)
qdf.SQL = sqlstring
MsgBox (qdf.SQL)

' update the recordset for the subform'

'Form![Contractor_Rates_subform].Form.Visible = True'
'Me.Contractor_Rates_subform.SourceObject = qdf.SQL'
Me!Contractor_Rates_subform.Form.Recordset = qdf.SQL

'restore original query
qdf.SQL = original_sql

qdf.Close

End Function

I have clicked once on the control for the subform on the main form and it
appears the name of the control is Contractor_Rates_subform

but I get the error 2465 when I step through the code to the
Me!Contractor_Rates_subform.Form.Recordset = qdf.SQL.

Any advice

D
Click to expand...
Click to expand...
Click to expand...
 
Back
Top