newbie: Do I need a loop? or just increment the counter

[M Davidson]

Hello all,

I have a form (frmScores) that opens on the click event of a button on
another form (frmDORMain). frmScores is bound to tblScores. HERE'S THE
PROBLEM: I want to display the form 5 times in sequence (User enters a
record and then clicks new record 5x) and on each new record I want the
value of txtCategory to change like so, Cat1, Cat2, Cat3, Cat4, Cat5. Then
the form closes and another form (that already works) opens. The built in
navigation bar is disabled. (intentionally)

I have tried googling this... I can't find a similar situation. I've
checked my texts too. Any guidance will be greatly appreciated.

I know I need to declare a counter and base the category change off the
counter, but the problem is I'm having difficulty understanding what events
to tie the code to and which form or module to place the code in. (see my
feeble attempts below.)

Here's the table structure:

tblScores
txtScoreSerial 'autonumber
txtDORSerial 'autonumber linked M/1 to tblDORMain!txtDORSerial
txtScore ' the score
txtCategory 'the category of the score... that needs to cycle.

frmScores has 4 text boxes and a button:
txtScoreSerial
txtDORSerial
txtScore 'text box for score
txtCategory ' need it to cycle value
btnNext ' want it to save records and when all 5 are entered, close
frmScores and open next form.

I know I need to do something like:

Dim Counter as Integer
Counter = 1

Select Case Counter

Case 1
Me!txtCategory.value = "Cat1"

Case 2
Me!txtCategory.value = "Cat2"

Case 3
Me!txtCategory.value = "Cat3"

Case 4
Me!txtCategory.value = "Cat4"

Case 5
Me!txtCategory.value = "Cat5"

Case Else
DoCmd .closeForm... (well I forget that part... But I want to close
the form)
'I have that working elsewhere so I can cut and paste
that...
DoCmd.openForm 'open the narrative form... (the one that already
works)
End Select

' Then I need to increment the counter
Counter = Counter +1

<<>>
But where do I declare the counter? I have to set it to "1" the first time
but if I don't put that line "somewhere else", then everytime I run the
code, it'll just get "reset" back to "1." Right? And Where do I place the
Select Case ? Should I have a next button on the form and Increment the
counter on it's click, and rerun the case select on it's click?

This has got me whipped. Everything else is working but this chunk.

THanks,
Mike Davidson

 

[Steve Schapel]

Mike,

First of all (as an aside), I don't think you can have two Autonumber
data type fields in the tblScores table.

If I understand you correctly, you don't actually need to open the form.
What you are talking about is adding records to the table, isn't it?
So the form is not implicated. The 5 records can be added to the
tblScores table from the command button on the frmDORMain form. Here's
one possible approach...

Dim i As Integer
For i = 1 To 5
CurrentDb.Execute "INSERT INTO tblScores ( txtDORSerial,
txtCategory)" & _
" VALUES ( " & Me.txtDORSerial & ", 'Cat'"
& i )
Next i

 

[m davidson]

Mr. Schapel,


Thank you so much for the reply.

I think I understand what's happening in that line of code. It's an
SQL statement that is creating the records in a table. I really am
excited about the possibility because if I'm thinking correctly I will
also gain a few added benefits from the exercise. 1) I can base my
data entry form (frmScores) on a query that will use the txtDORSerial
as criteria. This means that I won't have to do alot of fancy form
navigation work because only the scores in the set will be available to
the user... less chance of corruption. Sounds great.

I copy/pasted the code but I'm getting an expected end of statement
error. Can you tell me the syntax for the "INSERT INTO" and "VALUES"
SQL statement. And am I understanding correctly the process that's
happening?

btw - that is correct, I incorrectly labeled the field txtDORSerial as
autonumber. It's an autonumber in another table, but in this table
it's a linking field.

Many Thanks,
Mike Davidson

 

[m davidson]

I have googled this and looked through the help files. I think I'm
missing a quotation mark somewhere. Maybe the problem is near the
concatenation of the variable at the end of the statement. The help
files are pretty lean.

 

[Steve Schapel]

Mike,

I apologise, the code example I gave you was obviously done in too much
of a hurry. Try it like this...

CurrentDb.Execute "INSERT INTO tblScores ( txtDORSerial, txtCategory
)" & _
" VALUES ( " & Me.txtDORSerial & ", 'Cat'"
& i & " ) "

 

[m davidson]

Mr. Schapel,

Please don't apologize. I sincerely appreciate any help what so
ever. At the very least I now realize that using SQL in this instance
will produce better results across the board.

I tried the new code, but I get a compile error of "Method or data
member not found." The click event is highlighted in yellow and the
cursor goes to and highlights ".txtDORSerial" behind the VALUE
statement (I omitted the "Me" portion in the forementioned sentence
because it is not highlighted on the screen when the error occurs. The
help file suggests to check to make sure that no names were referenced
incorrectly. They all seem to be referenced correctly (in the tables
and forms)

Again, I don't understand the INSERT INTO statement yet, so I don't
know exactly what to look for. But does it matter that on frmDORmain
txtDORSerial is an autonumber. In an INSERT INTO statement, do the
field type declarations matter (within the tables themselves?)? ie:
number vs. text.

I really do appreciate your guidance. This is the last big hurdle on
this project.
thanks again,
Mike Davidson

 

[Steve Schapel]

Mike,

The code I gave you already assumes that txtDORSerial is numerical, so
that should be ok. If it was text we would have needed some extra ''s.
And I am assuming that the code is being called from a command button
on a form which includes the txtDORSerial field. One way to test
whether it is really the txtDORSerial that is causing the problem is to
go into a new line in the code, and type Me.txt and as soon as you type
the txt bit the "intellisense" should provide you with some autocomplete
options that should include the desired txtDORSerial. If not, it will
give us a clue as to what is wrong. In the meantime, perhaps you could
post back and copy/paste the code you are using now into your message,
just in case someone can spot something there.