Row Source in Form crashes Access

R

ryguy7272

I am trying to program a Form with a Row Source linked to:
SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1;

This does not crash Access, but if I change the Row Source to this:
Sheet1

Access will crash.

I can't see any other data sources listed under Row Source. What do I need
to do to access the correct Row Source?

The name of the ComboBox is 'cboStatus' and the code that I am using is
listed below:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If
' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on
With Reports![rptStaff]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptStaff].FilterOn = False
End Sub

I got the code from here:
http://www.fontstuff.com/access/acctut19.htm

I am making some modifications to try to create a new way of reporting
(currently use Excel) for out department.

It must be something fairly simple...just can't seem to figure it out...

Regards,
Ryan---
 
K

KARL DEWEY

The Row Source -- SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1; is a SQL
statement pulling two fields (ID and PROD) from table or query Sheet1. ID
is probably an autonumber field and PROD the product name associated with
that ID. If you look down at the next property you will see -
Column Count set to 2,
Column Heads - does not matter,
Column Widths 0"; then a measurement in inches (display width for PROD)
Bound Column 1
If you enter only Sheet1 Access will not know what fields to pull.

What change are you trying to accomplish?
 
R

ryguy7272

In the example that I mentioned here:
http://www.fontstuff.com/access/acctut19.htm

There are three tables. These tables are referenced in 'frmReportFilter1'
Office:
Row Source Type = Table/Query
Row Source = tblOffices
Column Count = 1

Department:
Row Source Type = Table/Query
Row Source = tblDepartments
Column Count = 1

Do I need to set up three tables and link them using one-to-many
relationship? I thought I could just have one singe table and reference the
appropriate column in the 'Row Source'. This is extremely easy to do in
Excel. I just can't figure it out how to do it in Access. I suspect Access
is capable of doing this; it seems like a very powerful tool.

Any help would be greatly appreciated.

Regards,
Ryan---



--
RyGuy


KARL DEWEY said:
The Row Source -- SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1; is a SQL
statement pulling two fields (ID and PROD) from table or query Sheet1. ID
is probably an autonumber field and PROD the product name associated with
that ID. If you look down at the next property you will see -
Column Count set to 2,
Column Heads - does not matter,
Column Widths 0"; then a measurement in inches (display width for PROD)
Bound Column 1
If you enter only Sheet1 Access will not know what fields to pull.

What change are you trying to accomplish?
--
KARL DEWEY
Build a little - Test a little


ryguy7272 said:
I am trying to program a Form with a Row Source linked to:
SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1;

This does not crash Access, but if I change the Row Source to this:
Sheet1

Access will crash.

I can't see any other data sources listed under Row Source. What do I need
to do to access the correct Row Source?

The name of the ComboBox is 'cboStatus' and the code that I am using is
listed below:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If
' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on
With Reports![rptStaff]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptStaff].FilterOn = False
End Sub

I got the code from here:
http://www.fontstuff.com/access/acctut19.htm

I am making some modifications to try to create a new way of reporting
(currently use Excel) for out department.

It must be something fairly simple...just can't seem to figure it out...

Regards,
Ryan---
Click to expand...
Click to expand...
 
K

KARL DEWEY

The form has a Record Source.
Row Source is only for a Combo or List box. It is not for the whole form.
What has 'frmReportFilter1' got to do with your Row Source?

--
KARL DEWEY
Build a little - Test a little


ryguy7272 said:
In the example that I mentioned here:
http://www.fontstuff.com/access/acctut19.htm

There are three tables. These tables are referenced in 'frmReportFilter1'
Office:
Row Source Type = Table/Query
Row Source = tblOffices
Column Count = 1

Department:
Row Source Type = Table/Query
Row Source = tblDepartments
Column Count = 1

Do I need to set up three tables and link them using one-to-many
relationship? I thought I could just have one singe table and reference the
appropriate column in the 'Row Source'. This is extremely easy to do in
Excel. I just can't figure it out how to do it in Access. I suspect Access
is capable of doing this; it seems like a very powerful tool.

Any help would be greatly appreciated.

Regards,
Ryan---



--
RyGuy


KARL DEWEY said:
The Row Source -- SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1; is a SQL
statement pulling two fields (ID and PROD) from table or query Sheet1. ID
is probably an autonumber field and PROD the product name associated with
that ID. If you look down at the next property you will see -
Column Count set to 2,
Column Heads - does not matter,
Column Widths 0"; then a measurement in inches (display width for PROD)
Bound Column 1
If you enter only Sheet1 Access will not know what fields to pull.

What change are you trying to accomplish?
--
KARL DEWEY
Build a little - Test a little


ryguy7272 said:
I am trying to program a Form with a Row Source linked to:
SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1;

This does not crash Access, but if I change the Row Source to this:
Sheet1

Access will crash.

I can't see any other data sources listed under Row Source. What do I need
to do to access the correct Row Source?

The name of the ComboBox is 'cboStatus' and the code that I am using is
listed below:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If
' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on
With Reports![rptStaff]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptStaff].FilterOn = False
End Sub

I got the code from here:
http://www.fontstuff.com/access/acctut19.htm

I am making some modifications to try to create a new way of reporting
(currently use Excel) for out department.

It must be something fairly simple...just can't seem to figure it out...

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

frmReportFilter1 = name of the Form
If I manually type AON into the ComboBox named cboBroker, I get the results
I want, but for some reason, the Form will not work if I just try to set the
Row Source to the Table with the data in it, and set the Bound Column to 5,
which is the column where the Broker info is held in the Table. It has
crashed over 20 times this morning...


--
RyGuy


KARL DEWEY said:
The form has a Record Source.
Row Source is only for a Combo or List box. It is not for the whole form.
What has 'frmReportFilter1' got to do with your Row Source?

--
KARL DEWEY
Build a little - Test a little


ryguy7272 said:
In the example that I mentioned here:
http://www.fontstuff.com/access/acctut19.htm

There are three tables. These tables are referenced in 'frmReportFilter1'
Office:
Row Source Type = Table/Query
Row Source = tblOffices
Column Count = 1

Department:
Row Source Type = Table/Query
Row Source = tblDepartments
Column Count = 1

Do I need to set up three tables and link them using one-to-many
relationship? I thought I could just have one singe table and reference the
appropriate column in the 'Row Source'. This is extremely easy to do in
Excel. I just can't figure it out how to do it in Access. I suspect Access
is capable of doing this; it seems like a very powerful tool.

Any help would be greatly appreciated.

Regards,
Ryan---



--
RyGuy


KARL DEWEY said:
The Row Source -- SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1; is a SQL
statement pulling two fields (ID and PROD) from table or query Sheet1. ID
is probably an autonumber field and PROD the product name associated with
that ID. If you look down at the next property you will see -
Column Count set to 2,
Column Heads - does not matter,
Column Widths 0"; then a measurement in inches (display width for PROD)
Bound Column 1
If you enter only Sheet1 Access will not know what fields to pull.

What change are you trying to accomplish?
--
KARL DEWEY
Build a little - Test a little


:

I am trying to program a Form with a Row Source linked to:
SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1;

This does not crash Access, but if I change the Row Source to this:
Sheet1

Access will crash.

I can't see any other data sources listed under Row Source. What do I need
to do to access the correct Row Source?

The name of the ComboBox is 'cboStatus' and the code that I am using is
listed below:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If
' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on
With Reports![rptStaff]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptStaff].FilterOn = False
End Sub

I got the code from here:
http://www.fontstuff.com/access/acctut19.htm

I am making some modifications to try to create a new way of reporting
(currently use Excel) for out department.

It must be something fairly simple...just can't seem to figure it out...

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

In the row source put the following information --
SELECT [TableName].[FieldName] FROM [TableName]

Bound column will be 1 as that is the only field in the select statement.

Some people select an ID field and a text field that has the display text.
They in turn bind column 1 and set widths as 0"; 2.5" so the ID is not
displayed but the text is. In this case the select would look like this --
SELECT [TableName].[ID], [TableName].[FieldName] FROM [TableName]

--
KARL DEWEY
Build a little - Test a little


ryguy7272 said:
frmReportFilter1 = name of the Form
If I manually type AON into the ComboBox named cboBroker, I get the results
I want, but for some reason, the Form will not work if I just try to set the
Row Source to the Table with the data in it, and set the Bound Column to 5,
which is the column where the Broker info is held in the Table. It has
crashed over 20 times this morning...


--
RyGuy


KARL DEWEY said:
The form has a Record Source.
Row Source is only for a Combo or List box. It is not for the whole form.
What has 'frmReportFilter1' got to do with your Row Source?

--
KARL DEWEY
Build a little - Test a little


ryguy7272 said:
In the example that I mentioned here:
http://www.fontstuff.com/access/acctut19.htm

There are three tables. These tables are referenced in 'frmReportFilter1'
Office:
Row Source Type = Table/Query
Row Source = tblOffices
Column Count = 1

Department:
Row Source Type = Table/Query
Row Source = tblDepartments
Column Count = 1

Do I need to set up three tables and link them using one-to-many
relationship? I thought I could just have one singe table and reference the
appropriate column in the 'Row Source'. This is extremely easy to do in
Excel. I just can't figure it out how to do it in Access. I suspect Access
is capable of doing this; it seems like a very powerful tool.

Any help would be greatly appreciated.

Regards,
Ryan---



--
RyGuy


:

The Row Source -- SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1; is a SQL
statement pulling two fields (ID and PROD) from table or query Sheet1. ID
is probably an autonumber field and PROD the product name associated with
that ID. If you look down at the next property you will see -
Column Count set to 2,
Column Heads - does not matter,
Column Widths 0"; then a measurement in inches (display width for PROD)
Bound Column 1
If you enter only Sheet1 Access will not know what fields to pull.

What change are you trying to accomplish?
--
KARL DEWEY
Build a little - Test a little


:

I am trying to program a Form with a Row Source linked to:
SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1;

This does not crash Access, but if I change the Row Source to this:
Sheet1

Access will crash.

I can't see any other data sources listed under Row Source. What do I need
to do to access the correct Row Source?

The name of the ComboBox is 'cboStatus' and the code that I am using is
listed below:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If
' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on
With Reports![rptStaff]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptStaff].FilterOn = False
End Sub

I got the code from here:
http://www.fontstuff.com/access/acctut19.htm

I am making some modifications to try to create a new way of reporting
(currently use Excel) for out department.

It must be something fairly simple...just can't seem to figure it out...

Regards,
Ryan---
Click to expand...
Click to expand...
Click to expand...
 
R

ryguy7272

Thanks for all the help Karl! I finally got it working; went with this:
SELECT DISTINCT tblTFI.Broker FROM tblTFI ORDER BY tblTFI.Broker;
Bound Column = 1

I thought I tried that SELECT syntax a while back, but something just did
not work out for me before. Anyway, I have a little clean up work to do on
the Report side...should be trivial compared to what i just did. I know
Excel pretty darn well, and I only know Access a little bit. However, I can
already tell that Access is a VERY powerful tool, and I look forward to
learning the ins and outs as time goes on.

Thanks again Karl!!

--
RyGuy


KARL DEWEY said:
In the row source put the following information --
SELECT [TableName].[FieldName] FROM [TableName]

Bound column will be 1 as that is the only field in the select statement.

Some people select an ID field and a text field that has the display text.
They in turn bind column 1 and set widths as 0"; 2.5" so the ID is not
displayed but the text is. In this case the select would look like this --
SELECT [TableName].[ID], [TableName].[FieldName] FROM [TableName]

--
KARL DEWEY
Build a little - Test a little


ryguy7272 said:
frmReportFilter1 = name of the Form
If I manually type AON into the ComboBox named cboBroker, I get the results
I want, but for some reason, the Form will not work if I just try to set the
Row Source to the Table with the data in it, and set the Bound Column to 5,
which is the column where the Broker info is held in the Table. It has
crashed over 20 times this morning...


--
RyGuy


KARL DEWEY said:
The form has a Record Source.
Row Source is only for a Combo or List box. It is not for the whole form.
What has 'frmReportFilter1' got to do with your Row Source?

--
KARL DEWEY
Build a little - Test a little


:

In the example that I mentioned here:
http://www.fontstuff.com/access/acctut19.htm

There are three tables. These tables are referenced in 'frmReportFilter1'
Office:
Row Source Type = Table/Query
Row Source = tblOffices
Column Count = 1

Department:
Row Source Type = Table/Query
Row Source = tblDepartments
Column Count = 1

Do I need to set up three tables and link them using one-to-many
relationship? I thought I could just have one singe table and reference the
appropriate column in the 'Row Source'. This is extremely easy to do in
Excel. I just can't figure it out how to do it in Access. I suspect Access
is capable of doing this; it seems like a very powerful tool.

Any help would be greatly appreciated.

Regards,
Ryan---



--
RyGuy


:

The Row Source -- SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1; is a SQL
statement pulling two fields (ID and PROD) from table or query Sheet1. ID
is probably an autonumber field and PROD the product name associated with
that ID. If you look down at the next property you will see -
Column Count set to 2,
Column Heads - does not matter,
Column Widths 0"; then a measurement in inches (display width for PROD)
Bound Column 1
If you enter only Sheet1 Access will not know what fields to pull.

What change are you trying to accomplish?
--
KARL DEWEY
Build a little - Test a little


:

I am trying to program a Form with a Row Source linked to:
SELECT Sheet1.ID, Sheet1.PROD FROM Sheet1;

This does not crash Access, but if I change the Row Source to this:
Sheet1

Access will crash.

I can't see any other data sources listed under Row Source. What do I need
to do to access the correct Row Source?

The name of the ComboBox is 'cboStatus' and the code that I am using is
listed below:
Option Compare Database
Option Explicit

Private Sub cmdApplyFilter_Click()
Dim strBroker As String
Dim strProd As String
Dim strStatus As String
Dim strFilter As String
' Check that the report is open
If SysCmd(acSysCmdGetObjectState, acReport, "rptStaff") <>
acObjStateOpen Then
MsgBox "You must open the report first."
Exit Sub
End If
' Build criteria string for Broker field
If IsNull(Me.cboBroker.Value) Then
strBroker = "Like '*'"
Else
strBroker = "='" & Me.cboBroker.Value & "'"
End If
' Build criteria string for Product field
If IsNull(Me.cboProd.Value) Then
strProd = "Like '*'"
Else
strProd = "='" & Me.cboProd.Value & "'"
End If
' Build criteria string for Status field
If IsNull(Me.cboStatus.Value) Then
strStatus = "Like '*'"
Else
strStatus = "='" & Me.cboStatus.Value & "'"
End If

' Combine criteria strings into a WHERE clause for the filter
strFilter = "[Broker] " & strBroker & " AND [Prod] " & strProd & " AND
[Status] " & strStatus
' Apply the filter and switch it on
With Reports![rptStaff]
.Filter = strFilter
.FilterOn = True
End With
End Sub

Private Sub cmdRemoveFilter_Click()
On Error Resume Next
' Switch the filter off
Reports![rptStaff].FilterOn = False
End Sub

I got the code from here:
http://www.fontstuff.com/access/acctut19.htm

I am making some modifications to try to create a new way of reporting
(currently use Excel) for out department.

It must be something fairly simple...just can't seem to figure it out...

Regards,
Ryan---
Click to expand...
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

Form linked to Linked Excel Table via ComboBox 10
Issue with UNC Path (I think). Trying to build Dynamic Report 11
Seeking Help With VBA and SQL Instant Query 8
Using a Form to Create a Query. Problem with VBA code... 2
Filtering Unbound Subform from Main form with combo boxes HELP 2
Plase find error in code 1
Query form coding 9
Code error to query with 6 Combo boxes 2

Top