All Data not displayed

[Khartoum]

I have a form with a agents name field which records errors. I added another
form, an agent log so that when i opened it it would display a log of all
that agents errors (like a datasheet) but it is only displaying the 1 record.
See code below, has anybody any ideas how to display all:
Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Agent Conflict Log"
stLinkCriteria = "[UserName]=" & "'" & Me![UserName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click
End Sub

Thanks Kh

 

[Sprinks]

Khartoum,

What is the RecordSource of the form? Is it possible that it is filtering
the records beyond the Username field. Also, check the RecordSource itself
to verify that multiple records are there.

The syntax of your procedure looks correct.

Sprinks

 

[Khartoum]

The form is called LPF Errors and the filter looks correct. It is set as
follows:

Record Source...........LPF Errors
Filter.........................[UserName]='Smith, John'

where John Smith is the record i have open in the LPF Error form

is there something i a missing?

Khartoum,

What is the RecordSource of the form? Is it possible that it is filtering
the records beyond the Username field. Also, check the RecordSource itself
to verify that multiple records are there.

The syntax of your procedure looks correct.

Sprinks

I have a form with a agents name field which records errors. I added another
form, an agent log so that when i opened it it would display a log of all
that agents errors (like a datasheet) but it is only displaying the 1 record.
See code below, has anybody any ideas how to display all:
Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Agent Conflict Log"
stLinkCriteria = "[UserName]=" & "'" & Me![UserName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click
End Sub

Thanks Kh

 

[Sprinks]

Khartoum,

Is it possible that LPF Errors *itself* is filtering the records further,
that is, is it a query that selects for example, only a certain day's records
or some other subset? If you open LPF Errors, are there multiple records
there for John Smith that are *not* displayed when you press your command
button?

Sprinks

The form is called LPF Errors and the filter looks correct. It is set as
follows:

Record Source...........LPF Errors
Filter.........................[UserName]='Smith, John'

where John Smith is the record i have open in the LPF Error form

is there something i a missing?

Khartoum,

What is the RecordSource of the form? Is it possible that it is filtering
the records beyond the Username field. Also, check the RecordSource itself
to verify that multiple records are there.

The syntax of your procedure looks correct.

Sprinks

I have a form with a agents name field which records errors. I added another
form, an agent log so that when i opened it it would display a log of all
that agents errors (like a datasheet) but it is only displaying the 1 record.
See code below, has anybody any ideas how to display all:
Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Agent Conflict Log"
stLinkCriteria = "[UserName]=" & "'" & Me![UserName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click
End Sub

Thanks Kh

 

[Khartoum]

There are no other filters and there are multiplle records, this is the SQL
for the query that runs the form:
SELECT [LPF Errors].[Error Number], [LPF Errors].UserName, [LPF
Errors].[Action Required], [LPF Errors].[Date of Action], [LPF
Errors].[Review Date], [LPF Errors].[Review Completed]
FROM [LPF Errors];
should there be a where clause in here to filter all occurences of the
username.
Regards Kh

Khartoum,

Is it possible that LPF Errors *itself* is filtering the records further,
that is, is it a query that selects for example, only a certain day's records
or some other subset? If you open LPF Errors, are there multiple records
there for John Smith that are *not* displayed when you press your command
button?

Sprinks

The form is called LPF Errors and the filter looks correct. It is set as
follows:

Record Source...........LPF Errors
Filter.........................[UserName]='Smith, John'

where John Smith is the record i have open in the LPF Error form

is there something i a missing?

Khartoum,

What is the RecordSource of the form? Is it possible that it is filtering
the records beyond the Username field. Also, check the RecordSource itself
to verify that multiple records are there.

The syntax of your procedure looks correct.

Sprinks

:

I have a form with a agents name field which records errors. I added another
form, an agent log so that when i opened it it would display a log of all
that agents errors (like a datasheet) but it is only displaying the 1 record.
See code below, has anybody any ideas how to display all:
Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Agent Conflict Log"
stLinkCriteria = "[UserName]=" & "'" & Me![UserName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click
End Sub

Thanks Kh

 

[Sprinks]

Khartoum,

You don't need a WHERE clause in the SQL since you are opening the form with
the optional WHERE parameter.

I created a table named LPF Errors and loaded two records with the UserName
= "Smith, John". If I paste your SQL into a new query, it returns both
records. If I cut and paste it into the RecordSource of a form, the form
displays both records.

I can't explain why your form is not displaying all records matching the
UserName, unless the UserName is a text field and the entries are not an
exact match, such as:

Smith, John
Smith,John
Smith John
Smith, John

If this is the case, a combo box that gets its rows from an Employee table
would be a better choice than to trust the user to enter the name correctly.

I am confused about one point. Your last post said that the RecordSource of
the form was "LPF Errors", and yet now you say that the SQL below "runs the
form". Since you cannot name a query the same name as an existing table,
"LPF Errors" must refer to the table, hence the SQL is NOT running the form,
the table is.

Sorry I couldn't help. Perhaps one of the MVPs can jump in here.

Sprinks

There are no other filters and there are multiplle records, this is the SQL
for the query that runs the form:
SELECT [LPF Errors].[Error Number], [LPF Errors].UserName, [LPF
Errors].[Action Required], [LPF Errors].[Date of Action], [LPF
Errors].[Review Date], [LPF Errors].[Review Completed]
FROM [LPF Errors];
should there be a where clause in here to filter all occurences of the
username.
Regards Kh

Khartoum,

Is it possible that LPF Errors *itself* is filtering the records further,
that is, is it a query that selects for example, only a certain day's records
or some other subset? If you open LPF Errors, are there multiple records
there for John Smith that are *not* displayed when you press your command
button?

Sprinks

The form is called LPF Errors and the filter looks correct. It is set as
follows:

Record Source...........LPF Errors
Filter.........................[UserName]='Smith, John'

where John Smith is the record i have open in the LPF Error form

is there something i a missing?

:

Khartoum,

What is the RecordSource of the form? Is it possible that it is filtering
the records beyond the Username field. Also, check the RecordSource itself
to verify that multiple records are there.

The syntax of your procedure looks correct.

Sprinks

:

I have a form with a agents name field which records errors. I added another
form, an agent log so that when i opened it it would display a log of all
that agents errors (like a datasheet) but it is only displaying the 1 record.
See code below, has anybody any ideas how to display all:
Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Agent Conflict Log"
stLinkCriteria = "[UserName]=" & "'" & Me![UserName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click
End Sub

Thanks Kh

 

[Khartoum]

Hi Spinks, will make myself more clear, probably not explaining it properly.
Have 2 tables 'Errors' and 'Teams'. Have one to many relationship from
(agents name from Teams) to (agents name from errors)
Have a query that has (agents name from errors) and (agents manager from
teams)
The errors form is created from the query so each time i select an agent,
the manager automatically populates.
I used the wizard whilst creating the (agent log of errors form) and used
the 'find specific data to display' option. used agents name as the matching
field expecting that whatever agents record the errors form was on at the
time, it would display all of their errors when clicking the log form. Have i
misunderstood this?
Hope this is clearer
obliged
Khartoum

Khartoum,

You don't need a WHERE clause in the SQL since you are opening the form with
the optional WHERE parameter.

I created a table named LPF Errors and loaded two records with the UserName
= "Smith, John". If I paste your SQL into a new query, it returns both
records. If I cut and paste it into the RecordSource of a form, the form
displays both records.

I can't explain why your form is not displaying all records matching the
UserName, unless the UserName is a text field and the entries are not an
exact match, such as:

Smith, John
Smith,John
Smith John
Smith, John

If this is the case, a combo box that gets its rows from an Employee table
would be a better choice than to trust the user to enter the name correctly.

I am confused about one point. Your last post said that the RecordSource of
the form was "LPF Errors", and yet now you say that the SQL below "runs the
form". Since you cannot name a query the same name as an existing table,
"LPF Errors" must refer to the table, hence the SQL is NOT running the form,
the table is.

Sorry I couldn't help. Perhaps one of the MVPs can jump in here.

Sprinks

There are no other filters and there are multiplle records, this is the SQL
for the query that runs the form:
SELECT [LPF Errors].[Error Number], [LPF Errors].UserName, [LPF
Errors].[Action Required], [LPF Errors].[Date of Action], [LPF
Errors].[Review Date], [LPF Errors].[Review Completed]
FROM [LPF Errors];
should there be a where clause in here to filter all occurences of the
username.
Regards Kh

Khartoum,

Is it possible that LPF Errors *itself* is filtering the records further,
that is, is it a query that selects for example, only a certain day's records
or some other subset? If you open LPF Errors, are there multiple records
there for John Smith that are *not* displayed when you press your command
button?

Sprinks

:

The form is called LPF Errors and the filter looks correct. It is set as
follows:

Record Source...........LPF Errors
Filter.........................[UserName]='Smith, John'

where John Smith is the record i have open in the LPF Error form

is there something i a missing?

:

Khartoum,

What is the RecordSource of the form? Is it possible that it is filtering
the records beyond the Username field. Also, check the RecordSource itself
to verify that multiple records are there.

The syntax of your procedure looks correct.

Sprinks

:

I have a form with a agents name field which records errors. I added another
form, an agent log so that when i opened it it would display a log of all
that agents errors (like a datasheet) but it is only displaying the 1 record.
See code below, has anybody any ideas how to display all:
Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Agent Conflict Log"
stLinkCriteria = "[UserName]=" & "'" & Me![UserName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click
End Sub

Thanks Kh

 

[Sprinks]

Khartoum,

The only explanations I can come up with are:

1. Your form is not a continuous form, and so displays only one record.
2. The RecordSource of the form is returning only one record. Based on the
code in your command button, the form *should* open with all records matching
the passed UserName. If it's not, then there is a disconnect between what
the form is based on vs. what you *think* it is.

To verify 1., check the form's DefaultView property; it should be set to
Continuous Forms.

To check 2., open your first form set to a name of which you know there are
multiple records. Open the 2nd form in Design View and copy the
RecordSource to the clipboard (highlight the contents of the RecordSource
property and press <Ctrl-C>)

Now open a new query. Change to SQL view, and cut and paste the copied
RecordSource SQL to the window. Return to QBE view with View, Design View.
In the Criteria Row of the UserName, type:

=Forms![YourFirstFormName]![YourUserNameControl]

This is the recordset your 2nd form *should* see when it's opened. Execute
the query. Does it return multiple records?

If it does, I'm at a loss to explain why the form won't display them.

Hope that helps.
Sprinks

Hi Spinks, will make myself more clear, probably not explaining it properly.
Have 2 tables 'Errors' and 'Teams'. Have one to many relationship from
(agents name from Teams) to (agents name from errors)
Have a query that has (agents name from errors) and (agents manager from
teams)
The errors form is created from the query so each time i select an agent,
the manager automatically populates.
I used the wizard whilst creating the (agent log of errors form) and used
the 'find specific data to display' option. used agents name as the matching
field expecting that whatever agents record the errors form was on at the
time, it would display all of their errors when clicking the log form. Have i
misunderstood this?
Hope this is clearer
obliged
Khartoum

Khartoum,

You don't need a WHERE clause in the SQL since you are opening the form with
the optional WHERE parameter.

I created a table named LPF Errors and loaded two records with the UserName
= "Smith, John". If I paste your SQL into a new query, it returns both
records. If I cut and paste it into the RecordSource of a form, the form
displays both records.

I can't explain why your form is not displaying all records matching the
UserName, unless the UserName is a text field and the entries are not an
exact match, such as:

Smith, John
Smith,John
Smith John
Smith, John

If this is the case, a combo box that gets its rows from an Employee table
would be a better choice than to trust the user to enter the name correctly.

I am confused about one point. Your last post said that the RecordSource of
the form was "LPF Errors", and yet now you say that the SQL below "runs the
form". Since you cannot name a query the same name as an existing table,
"LPF Errors" must refer to the table, hence the SQL is NOT running the form,
the table is.

Sorry I couldn't help. Perhaps one of the MVPs can jump in here.

Sprinks

There are no other filters and there are multiplle records, this is the SQL
for the query that runs the form:
SELECT [LPF Errors].[Error Number], [LPF Errors].UserName, [LPF
Errors].[Action Required], [LPF Errors].[Date of Action], [LPF
Errors].[Review Date], [LPF Errors].[Review Completed]
FROM [LPF Errors];
should there be a where clause in here to filter all occurences of the
username.
Regards Kh

:

Khartoum,

Is it possible that LPF Errors *itself* is filtering the records further,
that is, is it a query that selects for example, only a certain day's records
or some other subset? If you open LPF Errors, are there multiple records
there for John Smith that are *not* displayed when you press your command
button?

Sprinks

:

The form is called LPF Errors and the filter looks correct. It is set as
follows:

Record Source...........LPF Errors
Filter.........................[UserName]='Smith, John'

where John Smith is the record i have open in the LPF Error form

is there something i a missing?

:

Khartoum,

What is the RecordSource of the form? Is it possible that it is filtering
the records beyond the Username field. Also, check the RecordSource itself
to verify that multiple records are there.

The syntax of your procedure looks correct.

Sprinks

:

I have a form with a agents name field which records errors. I added another
form, an agent log so that when i opened it it would display a log of all
that agents errors (like a datasheet) but it is only displaying the 1 record.
See code below, has anybody any ideas how to display all:
Private Sub Command33_Click()
On Error GoTo Err_Command33_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Agent Conflict Log"
stLinkCriteria = "[UserName]=" & "'" & Me![UserName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Command33_Click:
Exit Sub

Err_Command33_Click:
MsgBox Err.Description
Resume Exit_Command33_Click
End Sub

Thanks Kh