Trying to port Code from MDB to ADP

[Kumba1]

I created a calendar form (actually looks like a calendar) in MS Access MDB that used dloopup and different pieces of code that now error in an ADP... the form is a 3x5 matrix of subforms, labeled form1 - form15, with each form being linked to text box that my code sets. Here is the code:


Private Sub Form_Load()
Dim x As Date
Dim y As Integer


'find out what next build date is based on scheduled builds
x = DMin(&quot;[BuildDate]&quot;, &quot;tblBuilds&quot;, &quot;[Built] = 0 and [inactive] = 0 and [location] <> &quot;&quot;Parts Room&quot;&quot;&quot
y = Weekday(x) - 1

'check for a saturday
If y = 6 Then y = 5

'start populating
Do Until y = 16

If y = 1 Then
Forms!frmcal!rptcal1date = x
Forms!frmcal!rptCal1.Visible = True
GoTo nextbuilddate

ElseIf y = 2 Then
Forms!frmcal!rptcal2date = x
Forms!frmcal!rptCal2.Visible = True
GoTo nextbuilddate

ElseIf y = 3 Then
Forms!frmcal!rptcal3date = x
Forms!frmcal!rptCal3.Visible = True
GoTo nextbuilddate

ElseIf y = 4 Then
Forms!frmcal!rptcal4date = x
Forms!frmcal!rptCal4.Visible = True
GoTo nextbuilddate

ElseIf y = 5 Then
Forms!frmcal!rptcal5date = x
Forms!frmcal!rptCal5.Visible = True
GoTo nextbuilddate

ElseIf y = 6 Then
Forms!frmcal!rptcal6date = x
Forms!frmcal!rptCal6.Visible = True
GoTo nextbuilddate

ElseIf y = 7 Then
Forms!frmcal!rptcal7date = x
Forms!frmcal!rptCal7.Visible = True
GoTo nextbuilddate

ElseIf y = 8 Then
Forms!frmcal!rptcal8date = x
Forms!frmcal!rptCal8.Visible = True
GoTo nextbuilddate

ElseIf y = 9 Then
Forms!frmcal!rptcal9date = x
Forms!frmcal!rptCal9.Visible = True
GoTo nextbuilddate

ElseIf y = 10 Then
Forms!frmcal!rptcal10date = x
Forms!frmcal!rptCal10.Visible = True
GoTo nextbuilddate

ElseIf y = 11 Then
Forms!frmcal!rptcal11date = x
Forms!frmcal!rptCal11.Visible = True
GoTo nextbuilddate

ElseIf y = 12 Then
Forms!frmcal!rptcal12date = x
Forms!frmcal!rptCal12.Visible = True
GoTo nextbuilddate

ElseIf y = 13 Then
Forms!frmcal!rptcal13date = x
Forms!frmcal!rptCal13.Visible = True
GoTo nextbuilddate

ElseIf y = 14 Then
Forms!frmcal!rptcal14date = x
Forms!frmcal!rptCal14.Visible = True
GoTo nextbuilddate

ElseIf y = 15 Then
Forms!frmcal!rptcal15date = x
Forms!frmcal!rptCal15.Visible = True
GoTo nextbuilddate

End If

nextbuilddate:
'advance to next build-date
x = x + 1
If Weekday(x) = 7 Then x = x + 2

'move to next grid
y = y + 1

'check for no-build days
If IsNull(DLookup(&quot;[index]&quot;, &quot;tblBuilds&quot;, &quot;[BuildDate] = #&quot; & x & &quot;#&quot) And y < 16 Then
GoTo nextbuilddate
End If

Loop

End Sub


The problems are within the DLookUp Parts. Thanks...

 

[KeARe]

Hi,

But what problems ?
Maybe that equal condition with date ?

K.

 

[MichaelRed]

Seems like a lot just to build a 'calendar'. I didn't (won't) go through all of it, but can say that a full month calendar can be generated in much less code. If you want to 'suppress' certain days of the week, just set their visible property to false and the width to some min, when generating it. There are several variations on the theme in thes fora which work well, so just search for them and see which suits your app.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Kumba1]

Well the reason there is so much code is I didn't know how to use a count in the variable, for instance, if I do a loop until x = 15, and the next line of code being x = x + 1, I dont know how to say the variable... if it were text I would do &quot;cal&quot; & x & &quot; and be done, but how do I call control cal1.visible = true, then control cal2.visible = true, then control cal3.visible = true, etc with x representing the level of the control I wanna use? if I could figure out that, I could reduce 30-lines of code to 7

be nice if I could quite simply do this:

dim x as int
dim y as date
y = date()
x = 1
do until x = 15
x = x + 1
y = y + 1
cal&quot; & x & &quot;.visible = true 'like cal1.visible = true
calcontrol&quot; & x & &quot;.value = y 'like calcontrol1.value = 3/3/2003
loop

 

[markphsd]

kumba,
try this:

dim i as integer
dim dt as date

for i = 1 to 15
me(&quot;cal&quot; & X).value = y '
next

or something like that.

the for next statement will &quot;loop&quot; through the numbers you want to do.

If you want to suppress visiblity, first set all visible to false, then as you add the date set it to visible.

so start with this.

for i = 1 to 15
me(&quot;cal&quot; & X).visible = false
next

I just recently learned it myself. It's a pretty useful thing i'm happy to pass on.




Mark P.

Bleh

 

[toddOne]

use dtpicker