type mismatch - need to default to current year 2

[jazminecat]

Hi all --

I have the following code in the onload event of a form designed for data entry.

    DoCmd.GoToRecord , , acNewRec
    Me.Year = Right(CStr(Year(Now())), 2)

I'm getting a type mismatch. What i want is for the year field, which is a combo box, to default to the current year. Without the line above, it loads blank, which is fine, but it'd be nice if it had the year already entered for the user.

Thanks in advance!

 

[PHV]

And what about this ?
Me.Year = Int(Right(Year(Now), 2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

still type mismatch.

in the table this field is text! this must be the problem, right?. it's 4 digits.
it's a combo box, and in the properties,
formet = blank
decimal places = auto
column count = 2
Column widths = 1,0

 

[jazminecat]

ok, there is a year table. it has a pk which is single digit, and the year is 4 digits and I changed it to number.

then in the main table, the pk is stored.
the form is based on this main table.

so, since the form is based on the main table, and in the main table only the pk is stored....is there even a way in this combobox to get the current year as the default value?

thanks again for you expertise!!

 

[PHV]

Any chance you could post the RowSource of the combo ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

here it is:

SELECT [tblYear].[Year], [tblYear].[YearID] FROM tblYear ORDER BY [tblYear].[Year] DESC;

 

[PHV]

And what about this ?
Me!Year = DLookUp("YearID", "tblYear", "[Year]=" & VBA.Year(Now))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

Ya, I would agree PHV.
I guess this is all contigent upon, what the bound column is?
I would expect all your suggestions to work respectively,
once we determine what the bound column is, 1 or 2.

Bound column 1
Me.Year = Int(Right(Year(Now), 2)) (since jazmine changed it to integer)

Bound col 2
Me!Year = DLookUp("YearID", "tblYear", "[Year]=" & VBA.Year(Now))

 

[jazminecat]

Ah, Zion and PHV, you did it again! It was bound column 2, and now it works! Thanks both of you for your help, as always. stars all around!

 

[jazminecat]

oh boy - well changing that seems to have messed up a lot of my other VB code - and so i'm getting more datatype mismatches, and my filters are returning 'too few parameters'.

So, should I change it back to text, or post the piece of code that's now broken? Or start a new thread?

 

[Zion7]

No problem Jazmine, Just return your changes, as before.
Make coumn 1, text again.
I believe you changed, the nonbound field. Column 1?
So PHV's code, will still work on Col 2.

 

[jazminecat]

Ok. SO lemme make sure I understand correctly SO if

Col 1 = YearID = Autonumber
col 2 = Year = text.

and the combobox holding the year is bound to column two, with this as it's rowsource

SELECT tblYear.Year, tblYear.YearID
FROM tblYear
ORDER BY tblYear.Year DESC;

Then this should work in the onload event of the form

Me!Year = DLookUp("YearID", "tblYear", "[Year]=" & VBA.Year(Now))

RIght? I'll give it a shot and let you know what happens.

 

[jazminecat]

still getting datatype mismatch in criteria expression.


here's the form load event:

 DoCmd.GoToRecord , , acNewRec
   
    Me!Year = DLookup("YearID", "tblYear", "[Year]=" & VBA.Year(Now))

just for grins, I also tried it with Me.Year instead. and

Me.Year = VBA.Year(Now)

same error.

 

[jazminecat]

ok. still getting data type mismatch, and I can't figure out know why. This is the rowsource for this field:

SELECT [tblYear].[Year], [tblYear].[YearID] FROM tblYear ORDER BY [tblYear].[Year] DESC;

and the bound column is 2, so it's bound to the yearid the column count is 2, with widths of 1,0 - so it shows the year in the dropdown, not the year id.


the record source for the form is the bids table, which contains the yearid, not the actual year.

If i change the vb to say

Me.Year = 1, it will load the form correctly, and show 2003 (that's the record in the year table with a yearid of 1)

so....what now? it's looking at the yearid, not the actual year.

So, I just answered my own question. Change the bound column to 1, and change the code to this:

DoCmd.GoToRecord , , acNewRec
Me.Year = VBA.Year(Now)


and right now, that seems to work! not that i assume it will still work on Monday....or will work on all machines. But thanks for all your help. If there's anything I need to be aware of that could make this blow up, i'm listening!

 

[Zion7]

That's bazaar Jazmine.
Your 2nd last post you said,
you tried both, and got same error?

When you hardcoded the ID, it worked.

why wouldn't DLookup work?
was there no record in tblYear, = Year(Date)?(I doubt it)

So now you had to change the bound column.

So the value of the combo will change now.
Will that affect other procedures?
do you use the combo to search?

I hope not, ...glad it's working.

...still, DLookup, should've worked, with bound col2

your 3rd last post, mixed up the arrangement?
you said "ID col 1", but Rowsource said otherwise.

When you thanked PHV & I, you had ID, col2 & bound.
You used DLookUp & it worked.
I said change Col 1, back to text, so your other code won't fail. It shouldn't have affected the Combo load event???

sorry, anyhow, glad it's working.