Append Data (1 Viewer)

[Eddie Mason]

I have cribbed the following code from one of the ‘sample databases’ however what I would like it to do is to append ADate, AMonth and AYear into tblAppointments. Is there any way to achieve this?

If Me.months > 0 Then

Dim ad As Date
Dim f1 As Double
Dim f2 As Double
Dim ADate As Date
Dim AMonth As String
Dim AYear As Double

For rl = 1 To Me.List8.ListCount
Me.List8.RemoveItem (0)
Next rl

f1 = 2
f2 = Me.months + 1

For fd = Date To DateSerial(Year(Date), month(Date) + 1, 0)

If Weekday(fd) = 6 Then
f1 = 1
f2 = Me.months
End If

Next fd

For i = f1 To f2

ad = DateSerial(Year(Date), month(Date) + i, 0)

For k = 0 To 6

If Weekday(DateSerial(Year(ad), month(ad) + 1, 0) - k) = 3 Then

dd = DateSerial(Year(ad), month(ad) + 1, 14) - k

Me.List8.AddItem "Second Tuesday date. ;" & Format(dd, "dd-mmm-yyyy") & ";" & MonthName(month(dd)) & ";" & Year(dd)
ADate = Format(dd, "dd-mm-yyyy")
AMonth = MonthName(month(dd))
AYear = Year(dd)

End If

Next k

Next i

Else
MsgBox "Write first month in above text box"
End If

End Sub

Kind regards

Eddie

 

[pr2-eugin]

Could you Please use Code Tags when posting VBA Code?

 

[Eddie Mason]

My apologies I have now inserted in the Code tags:

I have cribbed the following code from one of the ‘sample databases’ however what I would like it to do is to append ADate, AMonth and AYear into tblAppointments. Is there any way to achieve this?

If Me.months > 0 Then

Dim ad As Date
Dim f1 As Double
Dim f2 As Double
Dim ADate As Date
Dim AMonth As String
Dim AYear As Double

For rl = 1 To Me.List8.ListCount
Me.List8.RemoveItem (0)
Next rl

f1 = 2
f2 = Me.months + 1

For fd = Date To DateSerial(Year(Date), month(Date) + 1, 0)

If Weekday(fd) = 6 Then
f1 = 1
f2 = Me.months
End If

Next fd

For i = f1 To f2

ad = DateSerial(Year(Date), month(Date) + i, 0)

For k = 0 To 6

If Weekday(DateSerial(Year(ad), month(ad) + 1, 0) - k) = 3 Then

dd = DateSerial(Year(ad), month(ad) + 1, 14) - k

Me.List8.AddItem "Second Tuesday date. ;" & Format(dd, "dd-mmm-yyyy") & ";" & MonthName(month(dd)) & ";" & Year(dd)
ADate = Format(dd, "dd-mm-yyyy")
AMonth = MonthName(month(dd))
AYear = Year(dd)

End If

Next k

Next i

Else
MsgBox "Write first month in above text box"
End If

End Sub

Kind regards

Eddie

 

[pr2-eugin]

Did you read Post#2 of the link I gave you?

 

[Eddie Mason]

I'm sorry I am a little confused I clicked the Post#2 link which took me to properly indented code.

kind regards,

Eddie

 

[CJ_London]

properly indented does not mean this

If Weekday(fd) = 6 Then
f1 = 1
f2 = Me.months
End If

it means this

If Weekday(fd) = 6 Then
    f1 = 1
    f2 = Me.months
End If

Without proper indendation your code is difficult to read and will discourage people from trying to help

 

[Eddie Mason]

My apologies again; I have now had a go at indenting the code and I hope this makes it clearer.

I have cribbed the following code from one of the ‘sample databases’ however what I would like it to do is to append ADate, AMonth and AYear into tblAppointments. Is there any way to achieve this?

Private Sub Command5_Click()
If Me.months > 0 Then

    Dim ad As Date
    Dim f1 As Double
    Dim f2 As Double
    Dim ADate As Date
    Dim AMonth As String
    Dim AYear As Double

For rl = 1 To Me.List8.ListCount
    Me.List8.RemoveItem (0)
    Next rl

    f1 = 2
    f2 = Me.months + 1

For fd = Date To DateSerial(Year(Date), month(Date) + 1, 0)



If Weekday(fd) = 6 Then
    f1 = 1
    f2 = Me.months
End If

Next fd


For i = f1 To f2
    
    ad = DateSerial(Year(Date), month(Date) + i, 0)

For k = 0 To 6

If Weekday(DateSerial(Year(ad), month(ad) + 1, 0) - k) = 3 Then

    dd = DateSerial(Year(ad), month(ad) + 1, 14) - k

    Me.List8.AddItem "Second Tuesday date. ;" & Format(dd, "dd-mmm-yyyy") & ";" & MonthName(month(dd)) & ";" & Year(dd)
    ADate = Format(dd, "dd-mm-yyyy")
    AMonth = MonthName(month(dd))
    AYear = Year(dd)

End If

    Next k

    Next i

Else
    MsgBox "Write first month in above text box"
End If

End Sub

Kind regards

Eddie

 

[CJ_London]

perhaps you can provide the link to the sample database you got this code from - this is still pretty much unreadable with all the loops and if statements. I can't work out what it is supposed to do.

You'll also need to expand on this

what I would like it to do is to append ADate, AMonth and AYear into tblAppointments

detail like what are the fields in tblAppointments (name and type) would be helpful

Plus what is List8? OK, perhaps it's a listbox, but what is its rowsource, is it multi select etc

What is Me.Months? Text/Numeric? Bound/Unbound?

 

[Eddie Mason]

I am sorry that I have caused some confusion. The code that I have used is from a sample database on the Forum; Last-Friday which returns in an unbound list box the last Friday in the number of months that has been specified in an unbound text box [months]. I have changed the last Friday to the second Tuesday in each month to suit my requirements. The list box is populated with: "Last Friday date. ;" & Format(dd, "dd-mmm-yyyy") & ";" & MonthName(month(dd)) & ";" & Year(dd).

What I wanted to do was instead of populating the list box was to append this data into a table if this is possible.

Kind regards

Eddie

 

[CJ_London]

You will need to use something like this pseudo code

Currentdb.Execute("INSERT INTO tblAppointments ( ADate, AMonth, AYear ) VALUES (#" & ADate & "#, '" & AMonth & "', " & AYear & ")")

I'm still not clear exactly how the code works but suggest it probabaly wants to go after the 'AYear = Year(dd)' line but with all those loops it may insert more than once

 

[Eddie Mason]

Hi CJ,

What you have given me works absolutely perfectly no more tediously looking through a diary each year. Many thanks for your help I really do appreciate it.

Kind regards,

Eddie