How to enter Dates from beginning, Mid, and End of Month? 1

[ffleitas]

Hello programmers,

I have this code in VB:

.Fields("PaymentsDate&quot = Me!DateOfPlan + i
.Fields("PaymentsDate&quot = Me!DateOfPlan + i
etc.
I would like to correct this code with the current month beginning, next month beginning, mid, and end of month dates.

Sincerely,
Felix

PS Any examples of vb code with dates would be great for me as a reference and research material.

 

[scriverb]

What is Me!DateOfPlan. Is that a date that you want to work off of? Or, do you want to use the Current System Date to create the requested date values?

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ffleitas]

Thanks for responding.

Me!DateofPlan is the date entered by the user and yes it is the date I would like to work off.

Thanks

 

[scriverb]

Below are expressions to calculate the dates that you requested using the form contro Me![DateOfPlan].

CurrentMonth Beginning:
DateSerial(Year(Me![DateOfPlan]), Month(Me![DateOfPlan]), 1)

NextMonth Beginning:
DateSerial(Year(Me![DateOfPlan]), Month(DateAdd("m",1,Me![DateOfPlan])), 1)

CurrentMonth Beginning:
DateSerial(Year(Me![DateOfPlan]), Month(Me![DateOfPlan]), 1)

CurrentMonth MidDate:
DateSerial(Year(Me![DateOfPlan]), Month(Me![DateOfPlan]), 15)

CurrentMonth Ending:
DateSerial(Year(Me![DateOfPlan]), Month(Me![DateOfPlan], DateSerial(Year(Me![DateOfPlan]), Month(DateAdd("m",1,Me![DateOfPlan])),1)-1))

You can now use each of those to make the field/form assignments as you see fit. Please post back if you have any problems or need further explaination.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ffleitas]

Great it worked. I just have another question. I hope you can help me?

.Fields("PaymentsDate&quot = DateSerial(Year(Me![DateOfPlan]), Month(DateAdd("m", i, Me![DateOfPlan])), 10)

When I run this code it works great in terms of the months, nonetheless, after it creates Dec. 12/10/03 it creates Jan. 01/10/03 with the year 2003 rather than 2004. How can I fix this part?

Thanks,
Felix

 

[scriverb]

Try this. You have to perform the DateAdd for the month after the entire date has been created so that the proper year will be attached:

.Fields("PaymentsDate&quot = DateAdd("m",1,DateSerial(Year(Me![DateOfPlan]), Month(Me![DateOfPlan]), 10))


I think that should do it for you. Post back if you have more questions or problems.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ffleitas]

You are a genious! Thank you very much.

I promise this is the last thing. I have this problem on a different form and subform attached:

The form runs this code when it exits:
Private Sub Form_Close()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Dim j As Integer
Dim stDocName As String

stDocName = "qryPickupSubPlusQnty"

Set db = CurrentDb
Set rs = db.OpenRecordset("tblPickupsSub", dbOpenDynaset)

If Me!frmPickupsOnCallSub![Quantity] > 1 Then

With rs
j = 1
For i = 1 To Me!frmPickupsOnCallSub![Quantity]
.AddNew
.Fields("PickupID&quot = Me![PickupID]
.Fields("ContainerID&quot = Me!frmPickupsOnCallSub![ContainerID]
.Fields("BarCode&quot = Me!frmPickupsOnCallSub![BarCode]
.Fields("ContainerName&quot = Me!frmPickupsOnCallSub![ContainerName]
.Fields("ContainerDescription&quot = Me!frmPickupsOnCallSub![ContainerDescription]
.Fields("Quantity&quot = 1
.Update
Select Case i
Case 4, 8, 12, 16: j = j + 1
End Select
Next i
End With

Set rs = Nothing
Set db = Nothing

Else

End If

DoCmd.OpenQuery stDocName
End Sub

The problem is that the data gets created for the last data row in the subform not all the data rows in the subform. How can I fix this?

 

[scriverb]

From what you posted you are performing an AddNew which is only going to be reflected in the last row or the row just added. If you want to update each existing row in the recordset you have to loop through all of the records.

If you need help with that please help by explaining the table, queries, and recordset descriptions. Tell me what it is you want to do and if the fields for the updates are already in the table.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ffleitas]

Hi,

The newly added records in the subform area are transferred to the same table in their respective quantities as a 1 unit. For example, if you insert quantity 5 when you exit the form it creates 5 unique records for that row entry as quantity 1 five times. What I am hoping is to perform that same procedure for all the entries inside the subform area. Your question about if the fields already exist in the table is not a problem for me now. I hope this gives you some insight.

Thanks,
Felix

 

[scriverb]

Give this code a try. I only showed the code that needed updating. Update your entire subroutine with these updates

If Me!frmPickupsOnCallSub![Quantity] > 1 Then
With rs
.MoveFirst
Do
j = 1
For i = 1 To Me!frmPickupsOnCallSub![Quantity]
.AddNew
.Fields("PickupID&quot = Me![PickupID]
.Fields("ContainerID&quot = Me!frmPickupsOnCallSub![ContainerID]
.Fields("BarCode&quot = Me!frmPickupsOnCallSub![BarCode]
.Fields("ContainerName&quot = Me!frmPickupsOnCallSub![ContainerName]
.Fields("ContainerDescription&quot = Me!frmPickupsOnCallSub![ContainerDescription]
.Fields("Quantity&quot = 1
.Update
Select Case i
Case 4, 8, 12, 16: j = j + 1
End Select
Next i
.MoveNext
Loop Until .EOF
End With


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ffleitas]

Hi again,

I tried to code but it goes into an endless loop until I press ctrl-break to stop it. Let me know what you think?


Sincerely,
Felix