Form/Listbox/Subfrom question.....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello-
I have created my main menu form. On this form I have a list box that lists all of the departments that have submitted data for this project. I want to be able to choose one or several departments and click on a button which would result in seeing all the appropriate results in my pre-designed form( Called: Record Retention) the department list is a sepeate list and only contains the deparments and the project coordinators. There are also 2 other tables that contain information that include the department and the department head. Should I link these tables?

LASTLY, I have a subform on on the Record Retention form that displase all related records. But if there is no record it just shows a big blank space... how do i make it so that the subform becomes inactive(grey)?

MUCH THANKS for your help
 
CAN ANYONE HELP ME WITH THIS??? it seems like everyone else is getting their questions answered

----- Rich wrote: -----

Hello-
I have created my main menu form. On this form I have a list box that lists all of the departments that have submitted data for this project. I want to be able to choose one or several departments and click on a button which would result in seeing all the appropriate results in my pre-designed form( Called: Record Retention) the department list is a sepeate list and only contains the deparments and the project coordinators. There are also 2 other tables that contain information that include the department and the department head. Should I link these tables?

LASTLY, I have a subform on on the Record Retention form that displase all related records. But if there is no record it just shows a big blank space... how do i make it so that the subform becomes inactive(grey)?

MUCH THANKS for your help
 
Hi Rich:

I hope I can help a bit with your first issue. I would suggest that in
your onclick event for your button you use something like the
following code to build a query of departments. Then use that query to
open the Record Retention form. If your query needs to be more
detailed ... build it in query design view and then use the SQL
statement as a model for your code.

Note this code assumes that the bound column of your list box contains
the DepartmentID.


Private Sub Button_Click()
On Error GoTo Err_Button_Click

Dim dbs As Database
Dim ListBox As Control
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Dim strLinkQry As String
Dim qdf As QueryDef

Set dbs = CurrentDb
Set ListBox = Me.DepartmentListBox
strCriteria = ""
strSQL = ""
strLinkQry = "qryRecordRetention"

'Build string of DepartmentIDs for use in Query
For Each varItem In ListBox.ItemsSelected
strCriteria = strCriteria & "(Departments.DepartmentID)= " &
Trim(ListBox.ItemData(varItem)) & " Or "
Next varItem

'Remove final "OR" from the string
strCriteria = Left(strCriteria, Len(strCriteria) - 4)

'Build SQL Statement
strSQL = "SELECT Departments.DepartmentID, [AddlTable.AddlField]"
strSQL = strSQL & "FROM Departments "
strSQL = strSQL & "WHERE (" & strCriteria & ");"

'Delete existing qryRecordRetention
If QueryExists(strLinkQry) = True Then
dbs.QueryDefs.Delete strLinkQry
End If

'Create new qryRecordRetention
Set qdf = dbs.CreateQueryDef(strLinkQry)
qdf.SQL = strSQL

Set dbs = Nothing

Exit_Button_Click:
Exit Sub

Err_Button_Click:
MsgBox ("Error # " & Str(Err.Number) & " was generated by " &
Err.Source & Chr(13) & Err.Description)
Resume Exit_Button_Click

End Sub

__________

Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(strQueryName))
End Function


I hope this helps.

Cheers,
Diane


RICH said:
CAN ANYONE HELP ME WITH THIS??? it seems like everyone else is getting their questions answered

----- Rich wrote: -----

Hello-
I have created my main menu form. On this form I have a list
Click to expand...
box that lists all of the departments that have submitted data for
this project. I want to be able to choose one or several departments
and click on a button which would result in seeing all the appropriate
results in my pre-designed form( Called: Record Retention) the
department list is a sepeate list and only contains the deparments and
the project coordinators. There are also 2 other tables that contain
information that include the department and the department head.
Should I link these tables?
 
Back
Top