Combining 2 text field into one

N

NFL

I created an unbound form to search for names by using a combo box and was
able to combine two fields into one. It works great and this is what I have
in the row sources:

SELECT Students.LastName & ", " & Students.FirstName FROM Students;

I also created a command button on the same form look for the names and to
place that data on the report. What I get is an error "Enter Parameter
Value" Expr1.

Is there a way I can combine the Last Name and First Name fields in one
textbox? Here is the code from the command button.

Private Sub Command25_Click()

On Error GoTo Err_Command25_Click

Me.cboFindName.SetFocus

If Me.cboFindName.Text = "" Then
MsgBox "You must select a student to view class schedule." _
& vbCrLf _
& " Please try again!", vbCritical, "Student Query Error Message"
Exit Sub
Else
End If

Dim stDocName As String

stDocName = "StudentsQueryReport"
DoCmd.OpenReport stDocName, acViewPreview

Exit_Command25_Click:
Exit Sub
Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
 
M

Marshall Barton

NFL said:
I created an unbound form to search for names by using a combo box and was
able to combine two fields into one. It works great and this is what I have
in the row sources:

SELECT Students.LastName & ", " & Students.FirstName FROM Students;

I also created a command button on the same form look for the names and to
place that data on the report. What I get is an error "Enter Parameter
Value" Expr1.

Is there a way I can combine the Last Name and First Name fields in one
textbox? Here is the code from the command button.

Private Sub Command25_Click()

On Error GoTo Err_Command25_Click

Me.cboFindName.SetFocus

If Me.cboFindName.Text = "" Then
MsgBox "You must select a student to view class schedule." _
& vbCrLf _
& " Please try again!", vbCritical, "Student Query Error Message"
Exit Sub
Else
End If

Dim stDocName As String

stDocName = "StudentsQueryReport"
DoCmd.OpenReport stDocName, acViewPreview

Exit_Command25_Click:
Exit Sub
Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...


The combo box row source query needs to include the primary
key field:

SELECT pkield, LastName & ", " & FirstName
FROM Students
ORDER BY LastName, FirstName

and the ColumnCount set to 2, BoundColumn set to 1 and
ColumnWidths set to
0;

Then the button's code can be more like:

Private Sub Command25_Click()

On Error GoTo Err_Command25_Click

If IsNull(Me.cboFindName) Then
MsgBox "You must select a student to view class
schedule." _
& vbCrLf _
& " Please try again!", vbCritical, "Student Query
Error Message"
Exit Sub
End If

Dim stDocName As String
Dim stCriteria As String
stDocName = "StudentsQueryReport"
stCriteria = "pkfield=" & Me.cboFindName
DoCmd.OpenReport stDocName, acViewPreview, , stCriteria

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click
End Sub

The "Enter Parameter Value" is coming from the report's
record source query or the report itself because Expr1 is
not in the query's field list. I suspect that you have a
calculated field in the query to try to deal with the names
issue instead of using the primary key field, but I can't
tell without seeing the Query's SQL view.
 
M

Marshall Barton

NFL said:
I created an unbound form to search for names by using a combo box and was
able to combine two fields into one. It works great and this is what I have
in the row sources:

SELECT Students.LastName & ", " & Students.FirstName FROM Students;

I also created a command button on the same form look for the names and to
place that data on the report. What I get is an error "Enter Parameter
Value" Expr1.

Is there a way I can combine the Last Name and First Name fields in one
textbox? Here is the code from the command button.

Private Sub Command25_Click()

On Error GoTo Err_Command25_Click

Me.cboFindName.SetFocus

If Me.cboFindName.Text = "" Then
MsgBox "You must select a student to view class schedule." _
& vbCrLf _
& " Please try again!", vbCritical, "Student Query Error Message"
Exit Sub
Else
End If

Dim stDocName As String

stDocName = "StudentsQueryReport"
DoCmd.OpenReport stDocName, acViewPreview

Exit_Command25_Click:
Exit Sub
Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click

End Sub
Click to expand...


The combo box row source query needs to include the primary
key field:

SELECT pkield, LastName & ", " & FirstName
FROM Students
ORDER BY LastName, FirstName

and the ColumnCount set to 2, BoundColumn set to 1 and
ColumnWidths set to
0;

Then the button's code can be more like:

Private Sub Command25_Click()

On Error GoTo Err_Command25_Click

If IsNull(Me.cboFindName) Then
MsgBox "You must select a student to view class
schedule." _
& vbCrLf _
& " Please try again!", vbCritical, "Student Query
Error Message"
Exit Sub
End If

Dim stDocName As String
Dim stCriteria As String
stDocName = "StudentsQueryReport"
stCriteria = "pkfield=" & Me.cboFindName
DoCmd.OpenReport stDocName, acViewPreview, , stCriteria

Exit_Command25_Click:
Exit Sub

Err_Command25_Click:
MsgBox Err.Description
Resume Exit_Command25_Click
End Sub

The "Enter Parameter Value" is coming from the report's
record source query or the report itself because Expr1 is
not in the query's field list. I suspect that you have a
calculated field in the query to try to deal with the names
issue instead of using the primary key field, but I can't
tell without seeing the Query's SQL view.
 
N

NFL

I don't understand the primary key field for the combo box. My primary key
for this query is LastName
 
N

NFL

I don't understand the primary key field for the combo box. My primary key
for this query is LastName
 
M

Marshall Barton

Are you saying that your database will never have two people
with the same last name? If not, you need some other way to
identify an individual.

If your population is so small that eveyone has a unique
last name, then use the LastName field where I used pkfield
and change to:
stCriteria = "LastName=""" & Me.cboFindName & """"

The important point here is to construct the WhereCondition
argument for the OpenReport method.

I will need to see your report's record source query, if
there's a problem with my approach.
 
M

Marshall Barton

Are you saying that your database will never have two people
with the same last name? If not, you need some other way to
identify an individual.

If your population is so small that eveyone has a unique
last name, then use the LastName field where I used pkfield
and change to:
stCriteria = "LastName=""" & Me.cboFindName & """"

The important point here is to construct the WhereCondition
argument for the OpenReport method.

I will need to see your report's record source query, if
there's a problem with my approach.
 
N

NFL

I'm trying to work with two tables. The combo box in the I'm using will
look at the Students table students with LastName (No Duplicates) and
FirstName(duplicates OK). The other table also contains I like to use is
called StudentsandClasses which has Lastname (duplicates ok) and FirstName
(duplicates OK), ClassID (duplicatesOK) and course dates (duplicates OK).


Students StudentsandClasses
1------------ to Many----- ooo

This dbase will get large

Hope this helps...

Marshall Barton said:
Are you saying that your database will never have two people
with the same last name? If not, you need some other way to
identify an individual.

If your population is so small that eveyone has a unique
last name, then use the LastName field where I used pkfield
and change to:
stCriteria = "LastName=""" & Me.cboFindName & """"

The important point here is to construct the WhereCondition
argument for the OpenReport method.

I will need to see your report's record source query, if
there's a problem with my approach.
--
Marsh
MVP [MS Access]

I don't understand the primary key field for the combo box. My primary key
for this query is LastName
Click to expand...
Click to expand...
 
N

NFL

I'm trying to work with two tables. The combo box in the I'm using will
look at the Students table students with LastName (No Duplicates) and
FirstName(duplicates OK). The other table also contains I like to use is
called StudentsandClasses which has Lastname (duplicates ok) and FirstName
(duplicates OK), ClassID (duplicatesOK) and course dates (duplicates OK).


Students StudentsandClasses
1------------ to Many----- ooo

This dbase will get large

Hope this helps...

Marshall Barton said:
Are you saying that your database will never have two people
with the same last name? If not, you need some other way to
identify an individual.

If your population is so small that eveyone has a unique
last name, then use the LastName field where I used pkfield
and change to:
stCriteria = "LastName=""" & Me.cboFindName & """"

The important point here is to construct the WhereCondition
argument for the OpenReport method.

I will need to see your report's record source query, if
there's a problem with my approach.
--
Marsh
MVP [MS Access]

I don't understand the primary key field for the combo box. My primary key
for this query is LastName
Click to expand...
Click to expand...
 
M

Marshall Barton

Sorry, I just don't see what that has to do with the
question.

Did you try my suggested code?

If you did, what was wrong with the result?
 
M

Marshall Barton

Sorry, I just don't see what that has to do with the
question.

Did you try my suggested code?

If you did, what was wrong with the result?
 

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

What is wrong with my code? 3
Button Coding for Reports 2
Option Button 4
Requery and stay on current record 14
Error "Object variable or With block variable not set" 2
E-MAIL TWO REPORTS THROUGH SAME COMMAND BUTTON 2
Need help with No Data event 6
Message Box to Display a count of Records 3

Top