Urgent Help Needed

T

tmdrake

I have built a main form containg 4 unbound combo boxes (ProjectID,
DisciplineName, SectionNumber and Last Name) giving the user a choice in
which to display information in a subform based on one or a combination of
selections from the combo boxes.

Form information:
record source (tblProject Staffing Resources)
Parent/Child Link - (ProjectID)

Subform information:
record source - qryProjectID
SQL view of query:

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].DisciplineName, [tblProject Staffing Resources].SectionNumber,
[tblProject Staffing Resources].[Staff Last Name], [tblProject Staffing
Resources].[Staff First Name], [tblProject Staffing Resources].[Discipline
Lead], [tblProject Staffing Resources].[Est Project Start Date], [tblProject
Staffing Resources].[Est Project End Date], [tblProject Staffing
Resources].EmployeeID
FROM [tblProject Staffing Resources]
WHERE ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID])) AND ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName])) AND ((([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber])) OR ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) AND (([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].SectionNumber)=[Forms]![frmProject
Staffing Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) Is Null)) OR ((([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].DisciplineName)=[Forms]![frmProject
Staffing Resources(1)]![DisciplineName]) AND (([Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) Is Null)) OR ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([Forms]![frmProject Staffing Resources(1)]![DisciplineName]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![SectionNumber]) Is Null))
OR ((([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![DisciplineName]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![SectionNumber]) Is Null));

Select Button (command button):
DoCmd.Requery qryProjectID

Now the problem is when a selection is made from the ProjectID combo box the
subform shows the correct results.

But when a selection is made from the other combo boxes (DisciplineName,
SectionNumber and LastName) nothing appears in the subform. And if a
combination of selections are made from more than one combo box nothing
appears once the Select button is clicked.

Will someone please help me figure out what is wrong?

Thanks
 
M

Maurice

I think that the combination of AND's and OR's might be in contradiction
here. My advice would be to split the statment up into smaller pieces first
to check if a double combination works before testing with four possible
combinations.
--
Maurice Ausum


tmdrake said:
I have built a main form containg 4 unbound combo boxes (ProjectID,
DisciplineName, SectionNumber and Last Name) giving the user a choice in
which to display information in a subform based on one or a combination of
selections from the combo boxes.

Form information:
record source (tblProject Staffing Resources)
Parent/Child Link - (ProjectID)

Subform information:
record source - qryProjectID
SQL view of query:

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].DisciplineName, [tblProject Staffing Resources].SectionNumber,
[tblProject Staffing Resources].[Staff Last Name], [tblProject Staffing
Resources].[Staff First Name], [tblProject Staffing Resources].[Discipline
Lead], [tblProject Staffing Resources].[Est Project Start Date], [tblProject
Staffing Resources].[Est Project End Date], [tblProject Staffing
Resources].EmployeeID
FROM [tblProject Staffing Resources]
WHERE ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID])) AND ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName])) AND ((([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber])) OR ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) AND (([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].SectionNumber)=[Forms]![frmProject
Staffing Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) Is Null)) OR ((([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].DisciplineName)=[Forms]![frmProject
Staffing Resources(1)]![DisciplineName]) AND (([Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) Is Null)) OR ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([Forms]![frmProject Staffing Resources(1)]![DisciplineName]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![SectionNumber]) Is Null))
OR ((([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![DisciplineName]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![SectionNumber]) Is Null));

Select Button (command button):
DoCmd.Requery qryProjectID

Now the problem is when a selection is made from the ProjectID combo box the
subform shows the correct results.

But when a selection is made from the other combo boxes (DisciplineName,
SectionNumber and LastName) nothing appears in the subform. And if a
combination of selections are made from more than one combo box nothing
appears once the Select button is clicked.

Will someone please help me figure out what is wrong?

Thanks
Click to expand...
 
T

tmdrake

Using the SQL provided in the previous post. Would you please give me an
example of what you are saying.

Thanks
--
tmdrake


Maurice said:
I think that the combination of AND's and OR's might be in contradiction
here. My advice would be to split the statment up into smaller pieces first
to check if a double combination works before testing with four possible
combinations.
--
Maurice Ausum


tmdrake said:
I have built a main form containg 4 unbound combo boxes (ProjectID,
DisciplineName, SectionNumber and Last Name) giving the user a choice in
which to display information in a subform based on one or a combination of
selections from the combo boxes.

Form information:
record source (tblProject Staffing Resources)
Parent/Child Link - (ProjectID)

Subform information:
record source - qryProjectID
SQL view of query:

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].DisciplineName, [tblProject Staffing Resources].SectionNumber,
[tblProject Staffing Resources].[Staff Last Name], [tblProject Staffing
Resources].[Staff First Name], [tblProject Staffing Resources].[Discipline
Lead], [tblProject Staffing Resources].[Est Project Start Date], [tblProject
Staffing Resources].[Est Project End Date], [tblProject Staffing
Resources].EmployeeID
FROM [tblProject Staffing Resources]
WHERE ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID])) AND ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName])) AND ((([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber])) OR ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) AND (([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].SectionNumber)=[Forms]![frmProject
Staffing Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) Is Null)) OR ((([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].DisciplineName)=[Forms]![frmProject
Staffing Resources(1)]![DisciplineName]) AND (([Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) Is Null)) OR ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([Forms]![frmProject Staffing Resources(1)]![DisciplineName]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![SectionNumber]) Is Null))
OR ((([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![DisciplineName]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![SectionNumber]) Is Null));

Select Button (command button):
DoCmd.Requery qryProjectID

Now the problem is when a selection is made from the ProjectID combo box the
subform shows the correct results.

But when a selection is made from the other combo boxes (DisciplineName,
SectionNumber and LastName) nothing appears in the subform. And if a
combination of selections are made from more than one combo box nothing
appears once the Select button is clicked.

Will someone please help me figure out what is wrong?

Thanks
Click to expand...
Click to expand...
 
M

Maurice

Sure, try using the SQL you've posted eleminate everything after the first
AND and then try running the statement again. If this works add one OR and
try again. The combination of AND and OR leads quickly to no results because
of the strict combination of data (records) you might have.


--
Maurice Ausum


tmdrake said:
Using the SQL provided in the previous post. Would you please give me an
example of what you are saying.

Thanks
--
tmdrake


Maurice said:
I think that the combination of AND's and OR's might be in contradiction
here. My advice would be to split the statment up into smaller pieces first
to check if a double combination works before testing with four possible
combinations.
--
Maurice Ausum


tmdrake said:
I have built a main form containg 4 unbound combo boxes (ProjectID,
DisciplineName, SectionNumber and Last Name) giving the user a choice in
which to display information in a subform based on one or a combination of
selections from the combo boxes.

Form information:
record source (tblProject Staffing Resources)
Parent/Child Link - (ProjectID)

Subform information:
record source - qryProjectID
SQL view of query:

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].DisciplineName, [tblProject Staffing Resources].SectionNumber,
[tblProject Staffing Resources].[Staff Last Name], [tblProject Staffing
Resources].[Staff First Name], [tblProject Staffing Resources].[Discipline
Lead], [tblProject Staffing Resources].[Est Project Start Date], [tblProject
Staffing Resources].[Est Project End Date], [tblProject Staffing
Resources].EmployeeID
FROM [tblProject Staffing Resources]
WHERE ((([tblProject Staffing Resources].ProjectID)=[Forms]![frmProject
Staffing Resources(1)]![ProjectID])) AND ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName])) AND ((([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber])) OR ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) AND (([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].SectionNumber)=[Forms]![frmProject
Staffing Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) Is Null)) OR ((([tblProject Staffing
Resources].SectionNumber)=[Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([tblProject Staffing Resources].DisciplineName)=[Forms]![frmProject
Staffing Resources(1)]![DisciplineName]) AND (([Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) Is Null)) OR ((([tblProject Staffing
Resources].DisciplineName)=[Forms]![frmProject Staffing
Resources(1)]![DisciplineName]) AND (([Forms]![frmProject Staffing
Resources(1)]![ProjectID]) Is Null) AND (([Forms]![frmProject Staffing
Resources(1)]![SectionNumber]) Is Null)) OR ((([tblProject Staffing
Resources].ProjectID)=[Forms]![frmProject Staffing Resources(1)]![ProjectID])
AND (([Forms]![frmProject Staffing Resources(1)]![DisciplineName]) Is Null)
AND (([Forms]![frmProject Staffing Resources(1)]![SectionNumber]) Is Null))
OR ((([Forms]![frmProject Staffing Resources(1)]![ProjectID]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![DisciplineName]) Is Null) AND
(([Forms]![frmProject Staffing Resources(1)]![SectionNumber]) Is Null));

Select Button (command button):
DoCmd.Requery qryProjectID

Now the problem is when a selection is made from the ProjectID combo box the
subform shows the correct results.

But when a selection is made from the other combo boxes (DisciplineName,
SectionNumber and LastName) nothing appears in the subform. And if a
combination of selections are made from more than one combo box nothing
appears once the Select button is clicked.

Will someone please help me figure out what is wrong?

Thanks
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

I have built a main form containg 4 unbound combo boxes (ProjectID,
DisciplineName, SectionNumber and Last Name) giving the user a choice in
which to display information in a subform based on one or a combination of
selections from the combo boxes.
Click to expand...

Rather than this monstrous query, I'd really suggest a different approach. You
can put a command button on the form, cmdSearch let's say, which would build
the SQL string in code; it would check each combo box in turn, ignore it if
it's NULL, and build an AND or OR clause as appropriate if it's not. Once
you've built the entire SQL string, use it as the Recordsource for the desired
form or report.

John W. Vinson [MVP]
 
T

tmdrake

Thanks John,

This way seem much simpler, however, I'm still lost. Could you please walk
me through this process, I am fairly new at using Access at this level.

Thanks
 
J

John W. Vinson

Thanks John,

This way seem much simpler, however, I'm still lost. Could you please walk
me through this process, I am fairly new at using Access at this level.

Thanks
Click to expand...

Something like:

Private Sub cmdRunSearch_Click()
Dim strSQL As String

strSQL = "SELECT [tblProject Staffing Resources].ProjectID, " _
& "[tblProject Staffing Resources].DisciplineName, " _
& "[tblProject Staffing Resources].SectionNumber, " _
& "[tblProject Staffing Resources].[Staff Last Name], " _
& "[tblProject Staffing Resources].[Staff First Name], " _
& "[tblProject Staffing Resources].[Discipline Lead], " _
& "[tblProject Staffing Resources].[Est Project Start Date], " _
& "[tblProject Staffing Resources].[Est Project End Date], " _
& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber] Then
strSQL = strSQL & " AND [SectionNumber] = '" & Me![SectionNumber] & "'"
End If

<etc for all the combo boxes>

Me.subMySubform.Form.RecordSource = strSQL



The True will let you build a valid SQL even if none of the combos are
selected.


John W. Vinson [MVP]
 
T

tmdrake

Thanks John,

Do I put this in the SQL view of the query or on the event tab for the
command button?
--
tmdrake


John W. Vinson said:
Thanks John,

This way seem much simpler, however, I'm still lost. Could you please walk
me through this process, I am fairly new at using Access at this level.

Thanks
Click to expand...

Something like:

Private Sub cmdRunSearch_Click()
Dim strSQL As String

strSQL = "SELECT [tblProject Staffing Resources].ProjectID, " _
& "[tblProject Staffing Resources].DisciplineName, " _
& "[tblProject Staffing Resources].SectionNumber, " _
& "[tblProject Staffing Resources].[Staff Last Name], " _
& "[tblProject Staffing Resources].[Staff First Name], " _
& "[tblProject Staffing Resources].[Discipline Lead], " _
& "[tblProject Staffing Resources].[Est Project Start Date], " _
& "[tblProject Staffing Resources].[Est Project End Date], " _
& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber] Then
strSQL = strSQL & " AND [SectionNumber] = '" & Me![SectionNumber] & "'"
End If

<etc for all the combo boxes>

Me.subMySubform.Form.RecordSource = strSQL



The True will let you build a valid SQL even if none of the combos are
selected.


John W. Vinson [MVP]
Click to expand...
 
P

Pieter Wijnen

No critisism but, to make it compatible with MSSQL, Oracle et al, I suggest
using "WHERE 1=1" instead of "WHERE True"

Pieter

John W. Vinson said:
Thanks John,

This way seem much simpler, however, I'm still lost. Could you please
walk
me through this process, I am fairly new at using Access at this level.

Thanks
Click to expand...

Something like:

Private Sub cmdRunSearch_Click()
Dim strSQL As String

strSQL = "SELECT [tblProject Staffing Resources].ProjectID, " _
& "[tblProject Staffing Resources].DisciplineName, " _
& "[tblProject Staffing Resources].SectionNumber, " _
& "[tblProject Staffing Resources].[Staff Last Name], " _
& "[tblProject Staffing Resources].[Staff First Name], " _
& "[tblProject Staffing Resources].[Discipline Lead], " _
& "[tblProject Staffing Resources].[Est Project Start Date], " _
& "[tblProject Staffing Resources].[Est Project End Date], " _
& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] &
"'"
End If
If Not IsNull(Me![SectionNumber] Then
strSQL = strSQL & " AND [SectionNumber] = '" & Me![SectionNumber] & "'"
End If

<etc for all the combo boxes>

Me.subMySubform.Form.RecordSource = strSQL



The True will let you build a valid SQL even if none of the combos are
selected.


John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

Thanks John,

Do I put this in the SQL view of the query or on the event tab for the
command button?
Click to expand...

Click the ... icon by the Click event for the command button, and put this
code - or to be more precise, code like this using your own form and control
names - into the VBA editor. You'll need an End Sub line (which the code
editor will give you for free along with the Sub line).

John W. Vinson [MVP]
 
T

tmdrake

Okay John,

Yes I am a dummy, what are the " & ' used for. Am I to substitue this with
specific information?
 
T

tmdrake

John,

After adding all the information, when selecting from the form I get the
following error message:
Run-time error '3163': The filed is too small to accept the amount of data
you attempted to add. Try inserting or pasting less data.

What does this mean and how do I fix it?

Thanks so much
--
tmdrake


John W. Vinson said:
Thanks John,

This way seem much simpler, however, I'm still lost. Could you please walk
me through this process, I am fairly new at using Access at this level.

Thanks
Click to expand...

Something like:

Private Sub cmdRunSearch_Click()
Dim strSQL As String

strSQL = "SELECT [tblProject Staffing Resources].ProjectID, " _
& "[tblProject Staffing Resources].DisciplineName, " _
& "[tblProject Staffing Resources].SectionNumber, " _
& "[tblProject Staffing Resources].[Staff Last Name], " _
& "[tblProject Staffing Resources].[Staff First Name], " _
& "[tblProject Staffing Resources].[Discipline Lead], " _
& "[tblProject Staffing Resources].[Est Project Start Date], " _
& "[tblProject Staffing Resources].[Est Project End Date], " _
& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber] Then
strSQL = strSQL & " AND [SectionNumber] = '" & Me![SectionNumber] & "'"
End If

<etc for all the combo boxes>

Me.subMySubform.Form.RecordSource = strSQL



The True will let you build a valid SQL even if none of the combos are
selected.


John W. Vinson [MVP]
Click to expand...
 
J

John W. Vinson

Okay John,

Yes I am a dummy, what are the " & ' used for. Am I to substitue this with
specific information?
Click to expand...

" is the VBA delimiter for string constants. You're creating a string strSQL
by concatenating pieces. The piece

"[tblProject Staffing Resources].DisciplineName, "

is just a constant text string, the letters, punctuation and blanks between
the quotemarks.

The ' is used as a syntactically required delimiter on search criteria for
Text fields. For example, the expression

strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"

will be converted to

AND [DisciplineName] = 'Database Design'

if that's the value of the discipline name in your form. If the field in the
table is numeric (or is a <yuck> Lookup Field) then you should omit the '
marks.

The & character concatenates strings - for example,

"Foo" & "Bar"

is a way of expressing the string FooBar.

John W. Vinson [MVP]
 
J

John W. Vinson

John,

After adding all the information, when selecting from the form I get the
following error message:
Run-time error '3163': The filed is too small to accept the amount of data
you attempted to add. Try inserting or pasting less data.

What does this mean and how do I fix it?

Thanks so much
Click to expand...

It means that you're trying to insert a bale of hay into a shoebox. It won't
fit.

Please post your actual code and indicate which line generates the error.

John W. Vinson [MVP]
 
T

tmdrake

John,

Here is the code:

Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblProject Staffing Resources].ProjectID, " _
& "[tblProject Staffing Resources].DisciplineName, " _
& "[tblProject Staffing Resources].SectionNumber, " _
& "[tblProject Staffing Resources].[Staff Last Name], " _
& "[tblProject Staffing Resources].[Staff First Name], " _
& "[tblProject Staffing Resources].[Discipline Lead], " _
& "[tblProject Staffing Resources].[Est Project Start Date], " _
& "[tblProject Staffing Resources].[Est Project End Date], " _
& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = '" & Me![SectionNumber] & "'"
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If

CREATING THE ERROR MESSAGE:

Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL

Thanks
 
D

Douglas J. Steele

You're missing a space between the last field name ([tblProject Staffing
Resources].EmployeeID) and the keyword FROM.
 
T

tmdrake

Hi Doug,

I'm not sure what you mean. I tried inserting a space after the last field
name, however I still get the same error message. Please explain.

Thanks
--
tmdrake


Douglas J. Steele said:
You're missing a space between the last field name ([tblProject Staffing
Resources].EmployeeID) and the keyword FROM.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmdrake said:
John,

Here is the code:

Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblProject Staffing Resources].ProjectID, " _
& "[tblProject Staffing Resources].DisciplineName, " _
& "[tblProject Staffing Resources].SectionNumber, " _
& "[tblProject Staffing Resources].[Staff Last Name], " _
& "[tblProject Staffing Resources].[Staff First Name], " _
& "[tblProject Staffing Resources].[Discipline Lead], " _
& "[tblProject Staffing Resources].[Est Project Start Date], " _
& "[tblProject Staffing Resources].[Est Project End Date], " _
& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] & "'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = '" & Me![SectionNumber] & "'"
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If

CREATING THE ERROR MESSAGE:

Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL

Thanks
Click to expand...
Click to expand...
 
D

Douglas J. Steele

The code

& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"

means that the resulting SQL will be:

[tblProject Staffing Resources].EmployeeIDFROM [tblProject Staffing
Resources] WHERE True"

It needs to be

[tblProject Staffing Resources].EmployeeID FROM [tblProject Staffing
Resources] WHERE True"

which means that your code needs to be

& "[tblProject Staffing Resources].EmployeeID " _
& "FROM [tblProject Staffing Resources] WHERE True"

(Note the space between the D and the closing quote in the first line)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmdrake said:
Hi Doug,

I'm not sure what you mean. I tried inserting a space after the last
field
name, however I still get the same error message. Please explain.

Thanks
--
tmdrake


Douglas J. Steele said:
You're missing a space between the last field name ([tblProject Staffing
Resources].EmployeeID) and the keyword FROM.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmdrake said:
John,

Here is the code:

Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblProject Staffing Resources].ProjectID, " _
& "[tblProject Staffing Resources].DisciplineName, " _
& "[tblProject Staffing Resources].SectionNumber, " _
& "[tblProject Staffing Resources].[Staff Last Name], " _
& "[tblProject Staffing Resources].[Staff First Name], " _
& "[tblProject Staffing Resources].[Discipline Lead], " _
& "[tblProject Staffing Resources].[Est Project Start Date], " _
& "[tblProject Staffing Resources].[Est Project End Date], " _
& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] &
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = '" & Me![SectionNumber] & "'"
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If

CREATING THE ERROR MESSAGE:

Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL

Thanks
Click to expand...
Click to expand...
Click to expand...
 
T

tmdrake

Doug,

Now that I've made the change (inserting the space), when I hit the Select
button, two parameter boxes popup, one asking for the last name and the
another asking for the first name. Once this information is entered, I get a
error message stating "You canceled the previous operation".

How do I fix this?

Thanks
--
tmdrake


Douglas J. Steele said:
The code

& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"

means that the resulting SQL will be:

[tblProject Staffing Resources].EmployeeIDFROM [tblProject Staffing
Resources] WHERE True"

It needs to be

[tblProject Staffing Resources].EmployeeID FROM [tblProject Staffing
Resources] WHERE True"

which means that your code needs to be

& "[tblProject Staffing Resources].EmployeeID " _
& "FROM [tblProject Staffing Resources] WHERE True"

(Note the space between the D and the closing quote in the first line)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmdrake said:
Hi Doug,

I'm not sure what you mean. I tried inserting a space after the last
field
name, however I still get the same error message. Please explain.

Thanks
--
tmdrake


Douglas J. Steele said:
You're missing a space between the last field name ([tblProject Staffing
Resources].EmployeeID) and the keyword FROM.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John,

Here is the code:

Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblProject Staffing Resources].ProjectID, " _
& "[tblProject Staffing Resources].DisciplineName, " _
& "[tblProject Staffing Resources].SectionNumber, " _
& "[tblProject Staffing Resources].[Staff Last Name], " _
& "[tblProject Staffing Resources].[Staff First Name], " _
& "[tblProject Staffing Resources].[Discipline Lead], " _
& "[tblProject Staffing Resources].[Est Project Start Date], " _
& "[tblProject Staffing Resources].[Est Project End Date], " _
& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName] &
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = '" & Me![SectionNumber] & "'"
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If

CREATING THE ERROR MESSAGE:

Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL

Thanks
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

So exactly what's in strSQL when you're assigning it as the RecordSource?
Put

Debug.Print strSQL

in your code directly in front of the line

Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL

then go to the Immediate window (Ctrl-G) to see what's there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmdrake said:
Doug,

Now that I've made the change (inserting the space), when I hit the Select
button, two parameter boxes popup, one asking for the last name and the
another asking for the first name. Once this information is entered, I
get a
error message stating "You canceled the previous operation".

How do I fix this?

Thanks
--
tmdrake


Douglas J. Steele said:
The code

& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"

means that the resulting SQL will be:

[tblProject Staffing Resources].EmployeeIDFROM [tblProject Staffing
Resources] WHERE True"

It needs to be

[tblProject Staffing Resources].EmployeeID FROM [tblProject Staffing
Resources] WHERE True"

which means that your code needs to be

& "[tblProject Staffing Resources].EmployeeID " _
& "FROM [tblProject Staffing Resources] WHERE True"

(Note the space between the D and the closing quote in the first line)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


tmdrake said:
Hi Doug,

I'm not sure what you mean. I tried inserting a space after the last
field
name, however I still get the same error message. Please explain.

Thanks
--
tmdrake


:

You're missing a space between the last field name ([tblProject
Staffing
Resources].EmployeeID) and the keyword FROM.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


John,

Here is the code:

Private Sub Select_Click()
Dim strSQL As String

strSQL = "SELECT [tblProject Staffing Resources].ProjectID, " _
& "[tblProject Staffing Resources].DisciplineName, " _
& "[tblProject Staffing Resources].SectionNumber, " _
& "[tblProject Staffing Resources].[Staff Last Name], " _
& "[tblProject Staffing Resources].[Staff First Name], " _
& "[tblProject Staffing Resources].[Discipline Lead], " _
& "[tblProject Staffing Resources].[Est Project Start Date], " _
& "[tblProject Staffing Resources].[Est Project End Date], " _
& "[tblProject Staffing Resources].EmployeeID" _
& "FROM [tblProject Staffing Resources] WHERE True"
If Not IsNull(Me![DisciplineName]) Then
strSQL = strSQL & " AND [DisciplineName] = '" & Me![DisciplineName]
&
"'"
End If
If Not IsNull(Me![SectionNumber]) Then
strSQL = strSQL & " AND [SectionNumber] = '" & Me![SectionNumber] &
"'"
End If
If Not IsNull(Me![ProjectID]) Then
strSQL = strSQL & " AND [ProjectId] = '" & Me![ProjectID] & "'"
End If
If Not IsNull(Me![LastName]) Then
strSQL = strSQL & " AND [LastName] = '" & Me![LastName] & "'"
End If

CREATING THE ERROR MESSAGE:

Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL

Thanks
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

Urgent Help Needed 10
Subform Records Disappearing 0
Help Please - Form Coding 0
Filling fields trhrough a search form 0
Having trouble with layout master slides 7
Please Please Help - Run-time Error 2
Urgent Help Need - Adding Date Selection to Main Form 6
Sharing Resources from different groups... need help 1

Top