Using Calendar Control for daily Schedular

[ThatNewGuy]

Hello, bear with me through this I'm obviously an Access newbie.

I've installed Calendar Control 10.0 into a form that I'm working on. The purpose of the form is to select products, quantity, and due dates in order to create a daily/weekly production schedule. The problem I'm running into is that I can only select one date for one product number per report. For example, if there is an order for the same product number that product might be due on 2/5/07 and 2/9/07. I can select both dates through the control but the only product and date that populates the report is obviously the last one from 2/9/07 becuz it's the last data in the field. Is there code that once the first date is selected it populates the report but then the field resets in order to select a later date for the same product so that two varying dates can be on one report for the same product.

The simple current code is:

Private Sub Date_Due_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
Calendar4.Visible = True
Calendar4.SetFocus
If Not IsNull(Date_Due) Then
Calendar4.Value = Date_Due.Value
Else
Calendar4.Value = Date
End If
End Sub

Any help would be appreciated. Thx

 

[CaptainD]

If I understand your problem, you want to store multiple date values but you're not showing where you are storing those values. Reading what you posted, your are setting the calendar control's value depending on what is in one textbox called Date_Due. Then you're problem jumps to a problem with a report.
If you want to set date values in different textboxes the create a function that passes the calendars value to the selected text box and save the values to your database. Your report should then be populated by a query that retrieves those dates as well as the other related bits of information.

 

[ThatNewGuy]

I currently have the form setup where you select the date from the pop up calendar which populates one text box on the form as well as a date column within the table or database. It already sets the calendars value to the text box and to the table as is. It works great with the current query I've build for the report as well. All of the relationships between the table, query, and report work fine.

The problem I'm trying to address revolves around using more than one date for one product. Right now there is only one date column for the info to be saved within the table so the last date selected is the date that is being used on the report. I'm looking to use a date for the report then after an event (like sending it to the report via a command button) the date resets within the master table or something to that affect.

 

[CaptainD]

First we have a terminology problem. By product, do you mean a record? I can have a database that list "Milk" as a product and I have 6 gallons of Milk with different expiration dates. Thus one product, six records, one for each gallon of milk.
iID Product ExpirationDate
1 'Milk' '1/1/2007'
2 'Orange Juice' '3/1/2007'
3 'Milk' '4/15/2007'
(
Two products, each with an experation date. You want to change iID = 1's expiration date to '4/15/2007' you would use an update statement or, if it is in a bound form, change the date and update the record through the control.

Or, some action triggers code to run the update statement.

What code are you trying to run and what event triggers it?

 

[ThatNewGuy]

Ok, each product number would also be considered a record...but they are all individual product numbers

ID product # Due Date
1 123 2/9/2007
2 1234 2/11/2007
3 12345 2/12/2007

Currently I will received an order that will require us to produce product # 123, 1234, and 12345. I will select all three of these products in the form and I will select their appropriate due date for each product #. These are all transfered to the report to use as a schedule. The problem I'm trying to address is if product # 123 is due on 2/9/07 as well as 2/12/07 that each due date for the same product # shows up on the report.

product # Due Date
123 2/9/07 and 2/12/07

I don't have an update statement at this point. What happens is I will select 2/9/07 and that is saved in one date column of the table. Then obviously as i select 2/12/07 it overwrites the previous date of 2/9/07 and that due date never shows up on the report because it only saves the last event selected which is the 2/12/07.

 

[CaptainD]

Ok, each product number would also be considered a record...but they are all individual product numbers

Wrong (at least in database terms)

The product number, as you show it, is no different then a name. You can call it "Milk", 123 or what ever, it is only something distiguished in a record. Take a checking account. Each check is distinguished from another by the check number but each is still a check. Each purchase is itself a record and the banks database would also have a unique ID number for each record.
iID Check # Amount TransDate
1 123 20.00 1/1/2007
2 124 30.00 1/2/2007

In your case, you have:

iID Product # SomeDate
1 123 1/1/2007
2 1234 1/1/2007
3 123 1/4/2007

Here you have 3 records, two products. If I wrote a select statment as such:

"SELECT [Product #], SomeDate FROM YourDatabase WHERE [Product #] = 123"

I would get two records:
123, 1/1/2007
123, 1/4/2007

For your report you would want to select all records within some boundry and in the report, group by say the [Product #]

So if I do.

"SELECT [Product #], SomeDate FROM YourDatabase"

and group the report on the [Product #] I would show two records for the first group with a separation and one record in the next group

If you are using a bound form, it shows one record at a time. Even if I have two textboxes tied to the same date field. Say you are looking at record iID 3 the two textboxes tied to SomeDate in the table might look like two date fields but they are only for one record. If I change either one of the text boxes it will change the value for iID 3 and when I bring it up in a report, you'll have the same date.

The unique ID is so you can work with each record as a separate entity. If I do:

"UPDATE YourDatabase SET [Product #] = 1234 WHERE [Product #] = 123"

I would change all the records where [Product #] was 123 to 1234 and you would now see this:

iID Product # SomeDate
1 1234 1/1/2007
2 1234 1/1/2007
3 1234 1/4/2007

If I do this:

"UPDATE YourDatabase SET [Product #] = 1234 WHERE iID = 3"

I would get:

iID Product # SomeDate
1 123 1/1/2007
2 1234 1/1/2007
3 1234 1/4/2007

So the product number is not a record number and my guess is, that is where the problem lies.

Hope that makes since.