Setting default vaulue?

[djclntn]

I’m going to pose a question that I don’t believe can be done, but as they say it never hurts to asks. My question pertains to default values in Forms. In my MLB (baseball) dbs I enter in the teams scores & results from the preceding day. So with my 'Date' field I though it would be wise to have my date field set as Date()-1 as the default value -- which works great for me.

Here’s where it gets tricky, I have another field called, ‘HomeAway’ E.g. Record-1 will have an ‘H” & Record-2 will have can ‘A” because the Home team results are enter first & then the Away team results are enter second.



This is how something like my Form (datasheet) looks:
Date HmAy ATeam AScore AResults
4/10/2012 H PHI 2 W
4/10/2012 R PIT 0 L
4/10/2012 H TEX 3 L
4/10/2012 R SEA 5 W
4/10/2012 H NYM 1 L
4/10/2012 R CIN 4 W

*Here’s what I’m driving at/asking. In the ‘HmAy’ field is there any way I can configure the default value to come up ‘H’ in the next new Record, “A” followed by the next new Record & then repeat the cycle? As I said I can’t see how this wouldwork, but maybe someone might have an idea.

Thanks

[TG_W]

What about adding a field for identifying the game number for the day (which could also populate automatically) and running an If...Then statement where "H" is the default where game number and date are a new record? It should also allow you to run an If...Then on the AResults to auto populate there, as well.

[June7]

With only the two values, this might be fairly easy.

Your description says values are 'H' and 'A' but the sample shows 'H' and 'R'. I assume 'A' is correct.

In the GotFocus event of the HmAy control (combobox?) have code to lookup the HmAy value for the latest record in the table and set the value in current record with the opposite. Need a unique ID field, such as an autonumber.
Me.comboboxname = IIf(DLookup("HmAy", "tablename", "ID=" & DMax("ID", "tablename")) = "A", "H", "A")

This does not set the DefaultValue property. It actually sets value of the field.

[djclntn]

June, Correction, actually the "A" in a "R" I tried to follow your instructions at best, but it's not working. I made the 'HmRd' into a combo box & in the On Got Focus I have this expression, HmRd = IIf(DLookup("HmAy", "Reg Season", "ID=" & DMax("ID", "Reg Season")) = "A", "H", "A"). It's coming back telling me I need to save it as a mcro which I know nothing about macros. I think that I might be missing a step or two somewhere.

[June7]

The DLookup goes in VBA procedure.

In the GotFocus event property select [Event Procedure], double click the ellipses (...). This will put you in the procedure in the VBA editor. Type code.

You want to change the 'A' to an 'R'?

[djclntn]

In the Code window i have


Private Sub Combo80_GotFocus()
HmRd = IIf(DLookup("HmRd", "Reg Season Query", "ID=" & DMax("ID", "Reg Season Query")) = "A", "R", "A")

End Sub

I get this erroressage- Run-Time error 2471 The expression you have entered as a query parameter produced this error: 'ID'

[June7]

Whenever names have spaces or special characters or are reserved words must enclose in [].

The DLookup and DMax should search the table not query.

I was assuming there is a unique identifer field, perhaps an autonumber datatype. Is there and is it named ID?

Me.Combo80 = IIf(DLookup("HmRd", "[table name]", "ID=" & DMax("ID", "[table name]")) = "A", "R", "A")

Do you want to provide project?

[djclntn]

Here's the project/dbs. The Form I'm working on is, Reg Season Entries & the correct order is, "R", "H", "R"
The field 'RdHm' is where "R" & "H" are manually imputted. The field, 'RdHw' is where "R" & "H" is a Combo Box with the Code, RdHm = IIf(DLookup("RdHm", "Reg Season", "rsID=" & DMax("rsID", "Reg Season")) = "R", "H", "R")
When I add a new Record (as you can test for yoursef) the "RdHmA' doesn't fill in

[djclntn]

Correction,

The field, 'RdHwA' is where "R" & "H" is a Combo Box with the Code, RdHm = IIf(DLookup("RdHm", "Reg Season", "rsID=" & DMax("rsID", "Reg Season")) = "R", "H", "R")

[June7]

Where is there a combobox and a textbox both bound to RdHm?

The combobox is named Combo82 but the procedure is named Combo80_GotFocus. Correct the procedure name so they match and then set the GotFocus property of the combobox.

Revise code to:
Me.Combo82 = IIf(DLookup("RdHm", "[Reg Season]", "rsID=" & DMax("rsID", "[Reg Season]")) = "R", "H", "R")

[djclntn]

Still isn't working; when I input a team nothing appears in either 'RdHm' fld.

Here's the Code:
Private Sub Combo80_GotFocus()
Me.Combo82 = IIf(DLookup("RdHm", "[Reg Season]", "rsID=" & DMax("rsID", "[Reg Season]")) = "R", "H", "R")

End Sub

Do I need to delete the 'RdHm' combo box fielde & created another one maybe?

[June7]

What is Combo80? I did not see any combobox with that name.

I changed the procedure name to Combo82_GotFocus and set the GotFocus property of Combo82 to [Event Procedure].

Then whenever Combo82 gets focus the procedure runs.

[djclntn]

June,

What is wrong; I downloaded the dbs which I sent you, followed your instructions (at best-?) & when I enter in a new team/Record the 'RdHm' field is blank. Now are you telling me that you can enter new Records & either a "R" or a "H" appears in every other new Record entered?

[June7]

Yes, that is the case. Here is the revised db with my suggested edits. Actually, I included another change so the field only populates if it is null.

As soon as Combo82 (RdHmA) gets focus, it populates with H or R.

I notice some of the controls on the Reg Season Entries form don't allow edit/entry.

[djclntn]

June,

I'm very sorry to say but Iive downloaded your revised db & when I add a new team/record nothing is populated in the RdHm fld. I cannot understand what I'm doing wrong; why is it working for you, but not for me>

BTW thank you for your time, REALLY!