Simple form to update a table of dates

[Jane Schuster]

This should be simple but it isn't for me. I have two tables.

tblClient
ClientID
Name
Address, etc

tblSchedule
ScheduleID
ClientID
DueDate

I need a form for data entry that will bring up the ClientID and all of
their DueDates for modification, change and/or deletion. The DueDates are
generated outside the database and imported in to conform to some business
requirements and each client generally has between 10 and 20 DueDates, all
about three weeks apart, but occasionally we need to modify some of the
dates. Ideally, I would like the ClientName on the form, but I think I can
figure that out. Anyone have any ideas on how to get all of the DueDates on
one form to be updated?

 

[Allen Browne]

I take it you have a main form bound to tblClient, and a subform bound to
tblSchedule. Therefore the subform shows the due dates for the client in the
main form - each date on a row of its own (so the subform is in Continuous
or Datasheet view.)

Now you want to programmatically modify the existing dates for the selected
client in the main form? There are a couple of approaches, either looping
through the RecordsetClone of the subform, or executing an Update query.

This aircode example shows the code you could put into the event procedure
of a command button on the main form. It postpones all the client's dates by
the number of days you typed into the unbound text box named txtHowManyDays

Private Sub cmdAddDays_Click()
Dim db As DAO.Database
dim strSql as String

If IsNull(Me.ClientID) Or IsNull(Me.txtHowManyDays) Then
MsgBox "Requires client and number of days"
Else
strSql = "UPDATE tblSchedule SET DueDate = DueDate + " & _
Me.txtHowManyDays & " WHERE ClientID = " & Me.ClientID & ";"
Set db = dbEngine(0)(0)
db.Execute strSql, dbFailOnError
If db.RecordsAffected > 0 Then
Me.Sub1.Form.Requery
End If
set db = nothing
End If
End Sub

 

[Jane Schuster]

Oh, I am so not there. I don't have a main form bound to tblClient and a
subform bound to tblSchedule. I tried that with the wizard, but the dates
aren't showing up, or rather, the dates of a particular ClientID aren't
showing up, instead I am getting all DueDates for all Clients. The
relationship between the two tables is on the ClientID (it is a one to many).
I don't have a need to programatically modify all or even many of the
existing dates, it needs to be done individually for a small number of
clients (some get an extra day in a month because their original DueDate is a
holiday or some get their DueDate pushed back for another reason) I need to
be able to do this through data entry on a form so that a data entry person
doesn't have to wade through the tblSchedule to change the schedule. I'm
having a problem just setting up the form. Ideally, it would contain:

ClientID: _______________
ClientName_______________________

08/01/2008
08/10/2008
08/22/2008
etc
and the data entry person could modify the date on this screen, which would
modify the date in the tblschedule. Is that something that can be done?

 

[Allen Browne]

Okay, it sounds like the linking between main form and subform needs fixing.

Open the main form in design view.
Right-click on the edge of the subform control, and choose Properties.
On the Data tab of the Properties sheet, set:
Link Master Fields ClientID
Link Child Fields ClientID

This links the ClientID field on the main form to the ClientID on the
subform.

 

[Jane Schuster]

Yes, that was the problem. When fixed it does exactly what it should. Now
I've tried to enter a Combo Box to have the form contain just the ClientName
and the DueDates, but the option to link the Master and Child fields on
ClientID is not available. Does that mean its not possible to do this or am
I missing something once again?

 

[Allen Browne]

So as well as linking the subform to the main form on the ClientID, you also
want to filter the subform so it shows only the client's appointments in a
date range?

I assume you want the form to show all dates for the client when first
opened, and then to filter when you add limiting dates. You cannot use
LinkMasterFields/LinkChildFields for that.

What you could do is either apply a filter to the subform, or reassign its
RecordSource. Either way, you need to build the filter string in code, based
on whichever of the 2 unbound date text boxes you used. (Presumably there's
a starting date and and ending date.)

This example shows how to build such a filter string:
http://allenbrowne.com/casu-08.html
Look at Method 2 in the article. The filter string is named strWhere. After
building the string, it opens a report. In your case, you will apply it to
the filter of the subform. Therefore replace the "DoCmd.Openreport" line
with this:

If strWhere <> vbNullStrin Then
Me.[Sub1].Form.Filter = strWhere
Me.[Sub1].Form.FilterOn = True
Else
Me.[Sub1].Form.FilterOn = False
End If

Replace Sub1 with the name of your subform control.

 

[Jane Schuster]

Hold the phone. This is amazing, but it is way beyond me. I should have
posted my
first question in the "new and stupid users category". Thank you for your
patience, time and link to a your great website, but I'm definitely not ready
for filter strings and forms not bound to any query or table. I just figured
out combo boxes a couple of weeks ago. Again, I was not clear in what I was
trying to accomplish. I wanted to create a combo box that would allow a data
entry person to select a ClientName from the box (in alphabetical order) and
all of the client DueDates appear in the subform. The data entry person
could just pick one or more of the dates in the subform and modify or delete
any one of them. If your previous post addresses this, please just reply "do
it" and I'll spend the time figuring out filter strings and forms not bound
to any tables or queries.

So as well as linking the subform to the main form on the ClientID, you also
want to filter the subform so it shows only the client's appointments in a
date range?

I assume you want the form to show all dates for the client when first
opened, and then to filter when you add limiting dates. You cannot use
LinkMasterFields/LinkChildFields for that.

What you could do is either apply a filter to the subform, or reassign its
RecordSource. Either way, you need to build the filter string in code, based
on whichever of the 2 unbound date text boxes you used. (Presumably there's
a starting date and and ending date.)

This example shows how to build such a filter string:
http://allenbrowne.com/casu-08.html
Look at Method 2 in the article. The filter string is named strWhere. After
building the string, it opens a report. In your case, you will apply it to
the filter of the subform. Therefore replace the "DoCmd.Openreport" line
with this:

If strWhere <> vbNullStrin Then
Me.[Sub1].Form.Filter = strWhere
Me.[Sub1].Form.FilterOn = True
Else
Me.[Sub1].Form.FilterOn = False
End If

Replace Sub1 with the name of your subform control.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Yes, that was the problem. When fixed it does exactly what it should.
Now
I've tried to enter a Combo Box to have the form contain just the
ClientName
and the DueDates, but the option to link the Master and Child fields on
ClientID is not available. Does that mean its not possible to do this or
am
I missing something once again?

 

[Allen Browne]

Yes: the subform will show all the dates for the client in the main form.

You don't need any code or filter string to get that to work.

 

[Jane Schuster]

Thank you so much.

Yes: the subform will show all the dates for the client in the main form.

You don't need any code or filter string to get that to work.