You are here:

Using MS Access/New... Set the active form name into ActiveXCtl


QUESTION: Hi Scottgem

I have a table with one record in it.  It has the following fields;


The basis of my question is this, I want to replicate the first record as many number of times as the difference in days between [StayDate] and [DepartDate] less one day.

Example:  GuestName 'a' arrives on 22 Dec 2012 and departs morning of 28 Dec 2012.  Total of 6 nights.   I want to create a Do...Loop situation where the first record is used to create another five records, each new one a day later than the last, up to but not including the last date [departdate]

and so on.   Is this practical and can you help me wioth the coding?

many thanks in anticipation

ANSWER: Not a Do Loop, but a For...Next

First, however, I think you have a structural issue with your database. You should have a separate Guests table like so:

GuestID (PK Autonumber)

Then, you would use GuestID as a foreign key in your booking table instead of Guestname.

I'm going to assume you have a form where you enter the info into that record and you want to press a button to add the additional records. So the code behind that button would look like this:

Dim strSQL As String
Dim i As Integer

For i = 1 To DateDiff("d",Me.StayDate,Me.DepartDate)-1
   strSQL = "INSERT INTO tblBooking (GuestID, StayDate,DepartDate, RmNumber) " & _
         "VALUES(" & Me.cboGuest & ", #" & DateAdd("d",i,Me.StayDate) & "#, #" & _
         Me.DepartDate & "#, " & Me.RmNumber & ");"
   CurrentDB.Execute strSQL

Next i

This should do it. The For...Next loop should execute for the number of days between the two dates less one and The DateAdd function should increment the StayDate by the value of i (which increments with each pass of the loop.

Just make sure you use your correct field and control names.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------


I have a form with an ActiveXctl (Calendar) called ActiveXCtl0

At the moment it is working fine on that one form.   If I was to copy that form to a new form, then try and access the Calendar Control, it refers to the original form's name - of course.

The two forms are FrmRmQuery and FrmRmMaintenance.

It's working fine when I click the Calendar control on FrmRmQuery (the original form) as it has this code

   Forms!FrmRmQuery.StayDate.Value = Me.ActiveXCtl0.Value
   DoCmd.Close acForm, "Calendar2"

Is there a way to tell the Calendar control the name of the current form so it works on many forms, not just the FrmRmQuery form?

Really appreciate your help Scott, many thanks in anticipation :)

What version of Access are you using? Starting with 2007, there is a built in calendar control. I would not use the ActiveX control if I could avoid it.

If you are using earlier than 2007, I would use the form based calendars you will find here:

However, if you want to continue using the Activex control

Screen.ActiveForm.Name returns the name of the Active form.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.