Move Control (rectangle Bar) in form depending on start dates

[arunakumari02]

I have a form and I have to move the control i.e. the bar on the form depending on there start date in the table.

I tried this syntax but did not work
Forms!frmrest("recBar" & intRow).Left = rstProjects.DateStarted

It says" overflow" error.

Any hint on it.

 

[Tiro Cupidus]

I'm not certain about the exact cause of your error, but dates and control posiitions aren't comparable values. You need to pass DateStarted to a function that will return the desired position of the control (in twips).

 

[arunakumari02]

Can you give me any hint on how to convert date to twips?

 

[georgedwilkinson]

You can't. Why do you think that a date can set the position of a control? You're not telling us something.

 

[arunakumari02]

I have a date in the table.

In form i have a rectangular bars and when I open the form the rectangular bars should open according to the dates in the table.

In the form I have months from Jan to dec. In Table I have date in 01/01/2008. How to assign the date in the table to the form with months Jan to Dec

Could anyone give any hint on it, on how to align bars according to the dates in the table?

 

[apr pillai]

Date to Twips conversion

I don't know whether this will serve your purpose. As you already know Date is internaly stored as a number, 10th June 2008 is equallent to the number 39610. Twips measured 1 inch = 1440 twips. That is about 12 character per inch and each chacter space occupies 120 twips when printed on Paper.

This measurements goes for Forms also. You can down size the date value into a measurement which can be used within your Form.

www.msaccesstips.com

 

[Tiro Cupidus]

So the position of the bar should correspond to the month of the date? I think we're confused on what output you're looking for.

 

[arunakumari02]

The ouput of this should be the rectangular bars should allign according to the dates in the table

 

[georgedwilkinson]

Now we're getting somewhere. Multiply either the day of week (where Sunday or Monday = 1) or month of the year (January = 1) minus 1 times the width of your control. That should give you the "left" of the control.

Use the VBA function DatePart() to get the day or month.

 

[arunakumari02]

George, you said to multiply the month of the year(i.e. say july i.e 7) minus 1 times the width of the control.

Actually the width is in inches in the properties window. So I do I caluculate it.

Any hint is appreciated

 

[georgedwilkinson]

You need to find out how many twips wide your control is.

Use this code in your form's module:

debug.print me.mycontrol.width

and look in the immediate window.

 

[arunakumari02]

George,

I am doing this way (I have a recordset that gives the date from the table )
When I debugged it gave me the width of the bar but still confused of this.

I am getting error that item not found at this line Count = DatePart("m", rstProject!DateStarted)

Could you check the code I wrote:

Count = DatePart(rstProjects!DateStarted, "m")

Forms!frmPipeline("recBar" & intRow).Left = Count - Me("recBar" & intRow).Width


Debug.Print "recBar" & intRow
Debug.Print Me("recBar" & intRow).Width

 

[georgedwilkinson]

Look at your post. You state you have an error at a certain line but that line has the parameters in the wrong order.

The sentence in which you said there was an error had the correct syntax. The code you said to look at does not have the right syntax.

Change:

Count = DatePart(rstProjects!DateStarted, "m")

to:

Count = DatePart("m", rstProject!DateStarted)

But before that line, put this line:

Debug.Print rstProject!DateStarted

 

[arunakumari02]

George,

The datestarted worked.

Worked:
Debug.Print rstProjects!DateStarted

Count = DatePart("m", rstProjects!DateStarted)

Error:
Forms!frmPipeline("recBar" & intRow).Left = Count - Me("recBar" & intRow).Width ------------(1)

It says the "control or subform control is too large for this location"

I did not understand the logic behind of assigning the rectangular bar according to the date from the (1)

Thank you

 

[arunakumari02]

These statements worked fine before.

Debug.Print rstProjects!DateStarted
Count = DatePart("m", rstProjects!DateStarted)

But now I am having error:

It says "invalid use of Null" at this statement "Count = DatePart("m", rstProjects!DateStarted)"

Forms!frmPipeline("recBar" & intRow).Left = Count - Me("recBar" & intRow).Width

Any help is appreciated

 

[arunakumari02]

It is working partially but there is a small problem.

Actually the bars are assigning from the left side of the form. But I need bars after some width

The code:
Forms!frmRest("Bar" &Row).Left = intExtractMonth * 720


The output when I executed with the previous code:

checkbox ID name nowBarsStartHere
=x== 1 aa
x=== 2 bb


The output should look in this way

Any hint would be appreciated

checkbox ID name nowBarsStartHere
x 1 aa ====
x 2 bb ====

 

[Tiro Cupidus]

I think it would really help if you attach your database.

 

[arunakumari02]

Finally got it.