Access Code Syntax Query I Think!

[ColinKJ]

Hi All,

I'm very new to Access, and I'm building a Form.

On the form I have a Textbox to display the current period. I have a single record table which has one field for for the Current financial period of the Year, and one field for the current financial year.

tbl_Current_Period

When I open the form, I want to get the Current Period from the Table, and display it in the Textbox.

And I am trying the following:

Private Sub Form_Load()
Form1.Text18.Value = tbl_Current_Period!Period
End Sub

But I get the error message "Object Required"

Any suggestions please.

 

[boblarson]

You can't refer to a table or query like that. You would need a DLookup if that isn't in the form's recordsource.

Also, you don't refer to forms like Form1.Text18.Value. If you want the full reference it would be

Forms!Form1.Text18.Value (but you can leave off the .Value because that is the default)

And if this is the same form that is being loaded, just use the ME keyword:

Me.Text18 = Nz(DLookup("Period", "tbl_Current_Period"),0)

 

[ColinKJ]

Hi Bob,

That's works fine.

So that I learn as I go, can you just explain what "Nz(" means.

I changed the :

Me.Text18 = Nz(DLookup("Period", "tbl_Current_Period"), 0)

To:

Me.Text20 = Nz(DLookup("Year", "tbl_Current_Period"), 0)

And that picked up the Year field ok.

 

[ColinKJ]

Hi Bob,

Having got the current period from the Current Period table displayed in numeric form "10" & "2011", I also want to display it as the Calendar Month & and year.

As is common, the periods don't line up with the year, while the financial year is 2011, Period 1 is October 2010.

I have a table tbl_Months which has 12 records with 2 fields.

Field 1 is Period and runs 1 to 12, Field 2 is Month, and runs October to September.

With the values I got from the tbl_Current_Period I want to get:

Say Period 1, Year 2011 to display "October 2010"

With something like: If year <4 then year = year-1

then something like Month= ("Month", "tbl_Months "), Period)

Text5=Month & " " & year

Any guidance appreciated.

 

[boblarson]

Okay, NZ is Null-to-Zero and it helps avoid those nasty "Invalid Use of Null" errors if something doesn't match up in the table.

For the periods, I would think you would want is for the year to be based on the period selected. So, like this:

Me.Text20 = IIf(Nz(DLookup("Period","tbl_Current_Period"),0) < 4, Nz(DLookup("Year", "tbl_Current_Period"),0)-1,Nz(DLookup("Year", "tbl_Current_Period"),0))

But as for the month you should be able to build that from just the month value

Me.TextBoxNameHere = MonthName(DLookup("period", tbl_Current_Period"))

To put them together

Me.OtherTextBoxNameHere = Me.TextBoxNameHere & " " & Me.Text20

 

[ColinKJ]

Hi Bob,

We are definitely getting there.

The year part works fine, but the calendar month based on the period number isn't working.

At the moment, I have:

Private Sub Form_Load()
[FONT=Verdana][COLOR=black]Me.Text18 = Nz(DLookup("Period", "tbl_Current_Period"), 0)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Me.Text20 = Nz(DLookup("Year", "tbl_Current_Period"), 0)[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]Me.Text22 = Nz(DLookup("Month", "tbl_Months "), Me.Text18)[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Me.Text22 = Me.Text22 & " " & IIf(Nz(DLookup("Period", "tbl_Current_Period"), 0) < 4, Nz(DLookup("Year", "tbl_Current_Period"), 0) - 1, Nz(DLookup("Year", "tbl_Current_Period"), 0))[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End Sub[/COLOR][/FONT]

As I say, the year bit (last line) works fine when I change the current period.

It's this line:

Me.Text22 = Nz(DLookup("Month", "tbl_Months"), Me.Text18)

Doesn't mater what I do with it, it only gives me "October", which is the first record in the tbl_Months.

Me.Text18 is the Period Number.

Bob, Not sure what time zone you are in, but I'm in Spain and it's now 10:30pm, and I'm off the bed soon.

Thanks for your assistance, I'll be in touch again tomorrow if ok with you.

 

[boblarson]

I'm in the US in Oregon so it is still daytime here. If Me.Text18 is the period number you should be able to use

Me.Text22 = MonthName(Me.Text18)

 

[ColinKJ]

Thanks for that Bob.

Because our period numbers are not in line with the calendar months, I've created the following which does the job.

Private Sub Form_Load()
Me.Text18 = Nz(DLookup("Period", "tbl_Current_Period"), 0)
Me.Text20 = Nz(DLookup("Year", "tbl_Current_Period"), 0)
If Me.Text18 <= 3 Then x = Me.Text18 + 9 Else x = Me.Text18 - 3
Me.Text22 = MonthName(x)
Me.Text22 = Me.Text22 & " " & IIf(Nz(DLookup("Period", "tbl_Current_Period"), 0) < 4, Nz(DLookup("Year", "tbl_Current_Period"), 0) - 1, Nz(DLookup("Year", "tbl_Current_Period"), 0))
 
End Sub

Many thanks for your help.

Bob I've make another post:

http://www.mrexcel.com/forum/showthread.php?p=2820328#post2820328

If you're still up for more assistance

Thanks again.