Annie,
I think "geniuii" really translates as "stored bits of knowledge acquired by
hard work, finding what doesn't work, and collaboration with others at the
newsgroup".
I forgot that you might want to add more than one record.
You also
might want to scroll existing records and have the appropriate controls
disabled. The place to do this is in the form's OnCurrent event, which
occurs when you arrive on a record, be it existing or new.
Place the following code:
Dim i as Integer
If Nz(Me![NoSubjects])=0 Then
For i = 1 to 5 ' Turn them back on
Forms("Details").Controls("Subject" & Trim(Str(i))).Enabled = True
Next i
Else ' Set them according to [NoSubjects]
For i = [NoSubjects] + 1 to 5
Forms("Details").Controls("Subject" & Trim(Str(i))).Enabled = False
Next i
End If
You should also add basic error-handling so that any unforeseen conditions
do not crash your program but display a helpful message and recover
gracefully. Each subroutine should include error-handling. Just after the
Sub line, before the code starts:
On Error Goto ErrHandler
Then, after your code block, just above the End Sub line, place:
ErrExit:
Exit Sub
ErrHandler:
MsgBox "An error has occured. Please record the following
information." & _
vbCrLF & Err.Number & vbCrLf & Err.Description
You can customize what's between the quotes in the MsgBox call for your
particular situation.
Hope that helps.
Sprinks
Annie said:
Hi Sprinks
You guys are geniuses or is it geniuiii?
Just one thing though - I have to keep closing to make it work it doesn't
seem to lose the previous setting when I click new record. I want to have 5
subject fields so do I have to change my settings in the code from
[Nosubjects] + 1 to 5 (should this be 6?). Is there something else I need to
do to make it work each time I click New Record on the form???
Thanks a million!
--
Kind regards
Ann Shaw
Sprinks said:
Annie,
Be sure to distinguish between form *controls* (textbox, combo box, etc.)
and the table *fields* to which they are Bound.
The Name of a control is in its Name property, and the field to which it's
bound is in its ControlSource property. The code should use the values in
the Name property.
If the names of your controls are NoSubjects, Subject1, Subject2, etc., the
code should read:
Dim i as Integer
For i = [NoSubjects] + 1 to 5
Forms("Details").Controls("Subject" & Trim(Str(i))).Enabled = False
Next i
My apologies, but it also appears I was missing a close paren in my original
post. The open and close brackets around NoSubjects above could be omitted;
they just make it clear to the programmer and anyone who inherits the code
that we're talking about a control not a variable.
Hope that helps.
Sprinks
:
Hi
the code is on one line up to the word False but I am still getting compile
errors.
My form is called Details my 1st field is called NoSubjects and this looks
up a talbe called NoSubjects which is made up of
1
2
3
4
5
The next field in my form is Subject 1 and this looks up to a subject table
which lists all the subjects - I have 4 other fields in my form : Subject2,
Subject3, Subject4 and Subject5.
What I want is that when the user clicks on the dropdown list NoSubjects and
picks for example 3 then I want Subject4 and Subject5 boxes to gray out and
not be available. I am typing in the following code:
- I am clicking on the properties for NoSubjects (which is a lookup to
another table) and in the Event area After Update I am clicking ... and going
into the code builder - and typing:
Dim i as Integer
for i = cboNoSubjects + 1 to 5
Form("Details").Controls("cboSubject1?????? - I don't know what I
should be typing in here - & Trim(Str(i)).Enabled = False
and it is at this point when I press enter to move down that the text
changes to red and a compile error comes up????? I don't know VBA so I may
as well be looking into a deep black hole!!
Any enlightenment would be gratefully accepted!!
Many many thanks (sorry for the thesis but I am trying to explain exactly
what I want!
--
Kind regards
Ann Shaw
:
The following text is all supposed to be on one line:
Forms("Details").Controls("cboSubject" & Trim(Str(i)).Enabled = False
(it'll show up as two lines due to word-wrap)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Hi
I tried again by changing my combo box names. They are actually looking
up
other tables. In other words, NoSubjects is looking up a table that
contains
1 to 5 in number format. Each Subject field, Subject1, Subject2, etc. is
looking up a Subject table with the list of subjects.
I am typing the following code:
Dim i as Integer
For i = cboNoSubjects + 1 to 5
Forms("Details").Controls("cboSubject"&
Trim(Str(i)).Enabled = False
Next i
End Sub
The problem occurs after I type cboSubject &
I keep getting a compile error - I am not familiar with VBA what could be
the problem??
Frustrated!!
Ann
--
Kind regards
Ann Shaw
:
Hi Sprinks
My form is called details and my fields are as follows:
No of Subjects
Subject 1
Subject 2
Subject 3
Subject 4
Subject 5
I am clicking into the design of the form and in the properties I have
clicked AfterUpdate ... to get the code builder. I type in the following
Dim i as Integer
For i = cboNo of Subjects + 1 to 5
Forms("Details").Controls("cboSubject" & at this point when I
press
enter to continue the code I am getting a compile error.
Sorry - can you tell me what I am doing wrong?
Many thanks
Ann
--
Kind regards
Ann Shaw
:
Annie,
You can only do this in VBA, but it's quite easy--you will set the
Enabled
property of the form *control* (textbox, combo box, etc.) that is Bound
to
the Fields greater than the combo box bound to the No. of Subjects.
I'll assume they are all combo boxes named cboNumSubjects, cboSubject1,
etc.
The code for the AfterUpdate event of cboNumSubjects is:
Dim i as Integer
For i = cboNumSubjects + 1 to 5
Forms("YourForm").Controls("cboSubject" & Trim(Str(i)).Enabled =
False
Next i
This will "gray out" each of these controls and prevent the user from
accessing them.
You should be aware, however, that this is a non-normalized table
structure.
A normalized structure would be something like:
Registration
----------------------
RegistrationID AutoNumber (PK)
StudentID Integer (FK to Students)
RegistrationDetails
-----------------------
RegistrationDetailsID AutoNumber (PK)
RegistrationID Integer (FK to Registration)
SubjectID Integer (FK to Subjects)
Then you could implement a continuous subform for the detail table,
linked
to the main table by RegistrationID.
To insert the code, open the form in Design view, click on the No. of
Subjects combo box and show the properties (View, Properties). Click
on the
Event tab, then click into the AfterUpdate field. Click the ellipsis
that
appears on the right side of the field and choose CodeBuilder if a
dialog box
appears (it may go directly into the VBA Editor, depending on your
configuration).
Access will create a shell of an AfterUpdate procedure for you; cut and
paste the code between the Sub and End Sub lines, and change YourForm
to the
name of your form and cboSubject to whatever the name of your controls
are.
Save and close the VBA Editor window, save the form, and try it out.
Hope that helps.
Sprinks
:
Hi
I have a student registration form in Access and have the following
fields:
No. of Subjects
Subject 1
Subject 2
Subject 3
Subject 4
Subject 5
The No. of Subjects field is set as a look up to a table that
contains
number 1-5 and is limited.
What I would like to do is that when I pick the No. of Subjects for
example
2, then Subject 3 Subject 4 and Subject 5 fields cannot be used-
locked in
some way. When I pick No. of Subjects 4, then Subject 5 field
