[Q] Form/Subform auto list

Hi

I have a question on how to get a list of items from a table to populate to
the subform (rather than having to enter manually every time)
I currently have a form/subform that works fine but I have to enter the Dept
(Departments about 50 of them) manually each time rather than just entering
the sales info. The same entries happen every week. How do I have a form
that will automatically have list in the subform part? I will also need in
to be in a specific custom order which will be done by a field in the
department table(say SortOrder for the field name) Can you also tell me
where I can find an example of this.

Thanks
Kevin

Hi Kevin,

For the first part of your question, it appears to me as if you will need to
run an append query to automatically append the departsments to your join or
intersection table. This can be set up to run by clicking a button on the
main form, such that the VBA code runs an append query. Here is an example
that I use for a database. Hopefully you can follow it okay. Watch for word
wrap.

Private Sub cmdAddProjSchedRecords_Click()
On Error GoTo ProcError

'This procedure inserts a cartesian product set of records into
tblChgReqSchedItems
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()

'First, save the parent record, if it is dirty. Set focus back to this
subform, since the
'Form_Current event procedure that fires when the record is saved sets focus
to
' the "pgeGeneral" page.
If Me.Dirty = True Then
Me.Dirty = False
Me.TabOnDemand.Pages("pgeProjectSchedule").SetFocu s
End If

'Create insert statement to insert records into tblChgReqSchedItems
strSQL = "INSERT INTO tblChgReqSchedItems ( fkChangeRequest, fkProjSchedID )
" _
& "SELECT Chng_rqst.pkChangeRequest, tblChgReqSched.pkProjSchedID " _
& "FROM Chng_rqst, tblChgReqSched " _
& "WHERE Chng_rqst.pkChangeRequest= " & Me.Request_rk & " AND
tblChgReqSched.blnActiveSchdItem = -1;"

db.Execute strSQL 'Note: We do not want to include the optional
dbFailOnError
' parameter. If you do, a user could not add
a record back in,
' unless all records were first deleted.

'Requery subform
Me.ProjectScheduleContainer.[Form].Requery
Me.txtHidden.SetFocus
Me.cmdAddProjSchedRecords.Enabled = False
Me.txtSummaryTotalHours.Visible = True

ExitProc:
'Cleanup
On Error Resume Next
Set db = Nothing
Exit Sub
ProcError:
Select Case Err.Number
Case 2101 'Ignore this error
Case 3022 'Duplicates
MsgBox "You cannot add these records again.", _
vbCritical, "Attempt To Add Duplicate Records Detected..."
Me.txtHidden.SetFocus
Me.cmdAddProjSchedRecords.Enabled = False
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdAddProjSchedRecords_Click event
procedure..."
End Select
Resume ExitProc
End Sub



I'm not understanding the second part of your question, so if you could
expand on that a bit...


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"Kevin Labore" wrote:

Hi

I have a question on how to get a list of items from a table to populate to
the subform (rather than having to enter manually every time)
I currently have a form/subform that works fine but I have to enter the Dept
(Departments about 50 of them) manually each time rather than just entering
the sales info. The same entries happen every week. How do I have a form
that will automatically have list in the subform part? I will also need in
to be in a specific custom order which will be done by a field in the
department table(say SortOrder for the field name) Can you also tell me
where I can find an example of this.

Thanks
Kevin

HI Tom

What I have currently have is a subform:
I need to enter the the department names via a lookup query and then the
sales $ for each one like this:
toys $200
Food $200
Auto $100

instead of having to enter each department name for each line entry(as they
do not change although they could some of the seasonal ones (like summer)
might not have any sales for that week. I would like only to have to enter
the Sales$ figure and not the department name for entry. There are about 50
departments
so this would save time and help with errors.

The main form contains the week end date (ie. 12/8/07)
I have 3 tablesrelative to my question)
DeptDate (contains the date the sales occured)
DeptSalesDetail (Contains the DeptID, DeptDateID,Sales$, etc)
Depts (contains the Departments Name(Desc), Department #'s, CategoryID,
FilterCode)

I basically just want all the deparments Names to automaically go into the
subform so I just can enter the sales$ data. I want to filter by using a
field in the DEPT TABLE so the unsed Departments don't go in the Subform,
also this way I can have a custom order to match the printed report info(as
I can't get the data in an electronic form)
Hope this helps make it clearer.

Thanks
Kevin



"Tom Wickerath" AOS168b AT comcast DOT net wrote in message
...
Hi Kevin,

For the first part of your question, it appears to me as if you will need
to
run an append query to automatically append the departsments to your join
or
intersection table. This can be set up to run by clicking a button on the
main form, such that the VBA code runs an append query. Here is an example
that I use for a database. Hopefully you can follow it okay. Watch for
word
wrap.

Private Sub cmdAddProjSchedRecords_Click()
On Error GoTo ProcError

'This procedure inserts a cartesian product set of records into
tblChgReqSchedItems
Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()

'First, save the parent record, if it is dirty. Set focus back to this
subform, since the
'Form_Current event procedure that fires when the record is saved sets
focus
to
' the "pgeGeneral" page.
If Me.Dirty = True Then
Me.Dirty = False
Me.TabOnDemand.Pages("pgeProjectSchedule").SetFocu s
End If

'Create insert statement to insert records into tblChgReqSchedItems
strSQL = "INSERT INTO tblChgReqSchedItems ( fkChangeRequest,
fkProjSchedID )
" _
& "SELECT Chng_rqst.pkChangeRequest, tblChgReqSched.pkProjSchedID "
_
& "FROM Chng_rqst, tblChgReqSched " _
& "WHERE Chng_rqst.pkChangeRequest= " & Me.Request_rk & " AND
tblChgReqSched.blnActiveSchdItem = -1;"

db.Execute strSQL 'Note: We do not want to include the optional
dbFailOnError
' parameter. If you do, a user could not add
a record back in,
' unless all records were first deleted.

'Requery subform
Me.ProjectScheduleContainer.[Form].Requery
Me.txtHidden.SetFocus
Me.cmdAddProjSchedRecords.Enabled = False
Me.txtSummaryTotalHours.Visible = True

ExitProc:
'Cleanup
On Error Resume Next
Set db = Nothing
Exit Sub
ProcError:
Select Case Err.Number
Case 2101 'Ignore this error
Case 3022 'Duplicates
MsgBox "You cannot add these records again.", _
vbCritical, "Attempt To Add Duplicate Records Detected..."
Me.txtHidden.SetFocus
Me.cmdAddProjSchedRecords.Enabled = False
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in cmdAddProjSchedRecords_Click event
procedure..."
End Select
Resume ExitProc
End Sub



I'm not understanding the second part of your question, so if you could
expand on that a bit...


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"Kevin Labore" wrote:

Hi

I have a question on how to get a list of items from a table to populate
to
the subform (rather than having to enter manually every time)
I currently have a form/subform that works fine but I have to enter the
Dept
(Departments about 50 of them) manually each time rather than just
entering
the sales info. The same entries happen every week. How do I have a form
that will automatically have list in the subform part? I will also need
in
to be in a specific custom order which will be done by a field in the
department table(say SortOrder for the field name) Can you also tell me
where I can find an example of this.

Thanks
Kevin

Hi Kevin,

I basically just want all the deparments Names to automaically go into the
subform so I just can enter the sales$ data. I want to filter by using a
field in the DEPT TABLE so the unsed Departments don't go in the Subform,
also this way I can have a custom order to match the printed report info(as
I can't get the data in an electronic form).


I have a sample database that I am willing to share with you, but I do not
currently have it available for download. I believe it does everything you
have mentioned above, only with different table and field names. If you are
interested, send me a private e-mail message with a valid reply-to address.
Use the same subject as this thread. My e-mail address is available at the
bottom of the
contributor's page indicated below. Note: I need to leave for work now, so I
won't be able to reply until later tonight.

Please do not post your e-mail address (or mine) to a newsgroup reply. Doing
so will only attract the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/ex...tributors.html
__________________________________________

"Kevin Labore" wrote:

HI Tom

What I have currently have is a subform:
I need to enter the the department names via a lookup query and then the
sales $ for each one like this:
toys $200
Food $200
Auto $100

instead of having to enter each department name for each line entry(as they
do not change although they could some of the seasonal ones (like summer)
might not have any sales for that week. I would like only to have to enter
the Sales$ figure and not the department name for entry. There are about 50
departments
so this would save time and help with errors.

The main form contains the week end date (ie. 12/8/07)
I have 3 tablesrelative to my question)
DeptDate (contains the date the sales occured)
DeptSalesDetail (Contains the DeptID, DeptDateID,Sales$, etc)
Depts (contains the Departments Name(Desc), Department #'s, CategoryID,
FilterCode)

I basically just want all the deparments Names to automaically go into the
subform so I just can enter the sales$ data. I want to filter by using a
field in the DEPT TABLE so the unsed Departments don't go in the Subform,
also this way I can have a custom order to match the printed report info(as
I can't get the data in an electronic form)
Hope this helps make it clearer.

Thanks
Kevin