Urgent Help Needed

[tmdrake]

I have use this great forum so many times to help with different issues I
have had. I am hoping the same will happen this time.

Here we go:

I have created a form (recordsource [tblProject Staffing Resources]. On this
form there are 4 bound comboxes each linked to an individual table (exp.
Combox Box 1 = ProjectID linked to table ProjectID(2)) the same for the other
three DisciplineName, SectionNumber, and LastName. Each having a table
representing that field.

There is also a command button to click when any one or any combination of
the comboxes is chosen. So far this works. The code for this button is:

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].LastName, " _
& "[tblProject Staffing Resources].[FirstName], " _
& "[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
Debug.Print strSQL
Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL
End Sub

Secondly, on this form is a subform with 8 text boxes (the recordsource
=SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].DisciplineName, [tblProject Staffing Resources].SectionNumber,
[tblProject Staffing Resources].LastName, [tblProject Staffing
Resources].FirstName, [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 And
[DisciplineName]='Thermal' And [SectionNumber]=4470 ; based on the
selection made by one or more of the comboxes the results appear in a
Datasheet view on the subform. This also works fine.

The Problem: when all the comboxes are cleared (trying to write a code for a
command button that will clear any selections made and will allow another
selection to be made.) and you click on the subform the data from the first
line of the results disappear and that information is deleted from the table.

How do I fix this and how do I write the code to clear all selections made
in the combo box and allow another selection to be made and the results
showup on the subform (datasheet view)

Anyones help will be greatly appreciated.

Thanks

 

[John W. Vinson]

The Problem: when all the comboxes are cleared (trying to write a code for a
command button that will clear any selections made and will allow another
selection to be made.) and you click on the subform the data from the first
line of the results disappear and that information is deleted from the table.

It sounds like these are *bound* combo boxes, with a Control Source pointing
to a field in the table. Since you're not using these combos to update the
table, but rather to create a query as criteria, they should (MUST!!!) be
Unbound.

Set each of these combo's Control Source property to blank - just erase what's
there.

If you need combos for both updating and for searching... use two different
combos for each field.

John W. Vinson [MVP]

 

[tmdrake]

Thanks for the Help.

I changed all the combo boxes to unbound and that fixed the problem with it
deleting the data in the table. However, it created another issued: it will
only allow a search for a specific SectionNumber (4470) and DisciplineName
(Thermal). If any selections are made that does not include this criteria
then the results are blank.

I have check the codes for all the combo boxes and the select command button
and I can't identify why it will only return results for those two
selections. Please help.

Again thanks for your assistance.

 

[John W. Vinson]

Thanks for the Help.

I changed all the combo boxes to unbound and that fixed the problem with it
deleting the data in the table. However, it created another issued: it will
only allow a search for a specific SectionNumber (4470) and DisciplineName
(Thermal). If any selections are made that does not include this criteria
then the results are blank.

I have check the codes for all the combo boxes and the select command button
and I can't identify why it will only return results for those two
selections. Please help.

Again thanks for your assistance.

Do you still have the Debug.Print strSQL? What's the SQL string that your code
generates?


John W. Vinson [MVP]

 

[tmdrake]

Yes, this is the code that generates:

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].DisciplineName, [tblProject Staffing Resources].SectionNumber,
[tblProject Staffing Resources].LastName, [tblProject Staffing
Resources].[FirstName], [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 AND
[DisciplineName] = 'Thermal' AND [SectionNumber] = 4470

Now that I see why is only selecting this criteria; Where is it getting this
from? And how do I correct it?

As always you help is greatly appreciated.

 

[John W. Vinson]

Yes, this is the code that generates:

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].DisciplineName, [tblProject Staffing Resources].SectionNumber,
[tblProject Staffing Resources].LastName, [tblProject Staffing
Resources].[FirstName], [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 AND
[DisciplineName] = 'Thermal' AND [SectionNumber] = 4470

Now that I see why is only selecting this criteria; Where is it getting this
from? And how do I correct it?

I don't know... post your current code.

John W. Vinson [MVP]

 

[tmdrake]

Here go's:

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].LastName, " _
& "[tblProject Staffing Resources].[FirstName], " _
& "[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
Debug.Print strSQL
Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL
End Sub

Thanks
--
tmdrake

Yes, this is the code that generates:

SELECT [tblProject Staffing Resources].ProjectID, [tblProject Staffing
Resources].DisciplineName, [tblProject Staffing Resources].SectionNumber,
[tblProject Staffing Resources].LastName, [tblProject Staffing
Resources].[FirstName], [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 AND
[DisciplineName] = 'Thermal' AND [SectionNumber] = 4470

Now that I see why is only selecting this criteria; Where is it getting this
from? And how do I correct it?

I don't know... post your current code.

John W. Vinson [MVP]

 

[John W. Vinson]

Here go's:

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].LastName, " _
& "[tblProject Staffing Resources].[FirstName], " _
& "[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
Debug.Print strSQL
Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL
End Sub

Thanks

Evidently the DisciplineName and SectionNumber combo boxes on your form are
not empty. It's quite possible that Access is confused as to whether you mean
the *table field* DisciplineName or the *form control* - unbound now! - also
called DisciplineName.

Change the Name properties of these combo boxes to (for example)
cboFindDisciplineName, cboFindSectionNumber and so on, and change all the Me!
references in the code to refer to the control name.

John W. Vinson [MVP]

 

[tmdrake]

Thank you so much, this part of this form works perfectly.

Do you have any suggestions on the code to create a Command Button that will
clear all the ComoBoxes, and once the boxes are clear, a click on the Select
button displays all the records on the table in the subform.

This already works if the ComoBoxes are cleared manually and the then
select, all the table records appear.
--
tmdrake

Here go's:

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].LastName, " _
& "[tblProject Staffing Resources].[FirstName], " _
& "[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
Debug.Print strSQL
Me.Project_Staffing_Resources_subform.Form.RecordSource = strSQL
End Sub

Thanks

Evidently the DisciplineName and SectionNumber combo boxes on your form are
not empty. It's quite possible that Access is confused as to whether you mean
the *table field* DisciplineName or the *form control* - unbound now! - also
called DisciplineName.

Change the Name properties of these combo boxes to (for example)
cboFindDisciplineName, cboFindSectionNumber and so on, and change all the Me!
references in the code to refer to the control name.

John W. Vinson [MVP]

 

[John W. Vinson]

Thank you so much, this part of this form works perfectly.

Do you have any suggestions on the code to create a Command Button that will
clear all the ComoBoxes, and once the boxes are clear, a click on the Select
button displays all the records on the table in the subform.

This already works if the ComoBoxes are cleared manually and the then
select, all the table records appear.

Private Sub cmdShowAll_Click()
Me!cboFindDisciplineName = Null
Me!cboFindSectionNumber = Null
Me!cboFindProjectID = Null
Me!cboFindLastName = Null
Call Select_Click
End Sub

Note also that you should make one small change in the LastName combo: you're
using ' as a delimter, which will cause errors if the last name contains an
apostrophe (O'Brien for example). Change it to

strSQL = strSQL & " AND [LastName] = """ & Me![LastName] & """"

That's three " before the form reference, four after it; this uses two
doublequotes within the string to represent a single doublequote in the
result.

John W. Vinson [MVP]

 

[tmdrake]

Thank you so much, everything is working exactly how I wanted it to.

Again Thank you
--
tmdrake

Thank you so much, this part of this form works perfectly.

Do you have any suggestions on the code to create a Command Button that will
clear all the ComoBoxes, and once the boxes are clear, a click on the Select
button displays all the records on the table in the subform.

This already works if the ComoBoxes are cleared manually and the then
select, all the table records appear.

Private Sub cmdShowAll_Click()
Me!cboFindDisciplineName = Null
Me!cboFindSectionNumber = Null
Me!cboFindProjectID = Null
Me!cboFindLastName = Null
Call Select_Click
End Sub

Note also that you should make one small change in the LastName combo: you're
using ' as a delimter, which will cause errors if the last name contains an
apostrophe (O'Brien for example). Change it to

strSQL = strSQL & " AND [LastName] = """ & Me![LastName] & """"

That's three " before the form reference, four after it; this uses two
doublequotes within the string to represent a single doublequote in the
result.

John W. Vinson [MVP]