Take me to another form viewing a certain record

[Guest]

Hi,

I am still working on an Access database which holds records of clinical
trials studies. Each study has its own study number and title. When the
database starts up, the switchboard form appears first. I would like on here,
to be able to select from a drop down list the a certain record and after
clicking the title, study number or both, the form where I enter details for
these records/studies will view the record just selected from the list
box/drop down list I want on my switch board form. Can anyone describe a
non-complicated or step by step guide on how I could create this, as I do
most of my form features e.g command buttons, using the wizard toolbar. I'd
be most grateful.
Many thanks,
Liz.

 

[Michel Walsh]

Hi,


The DoCmd.OpenForm procedure allow a parameter, whereCondition, to specify,
as a string, a condition that limit the records to be displayed. If you have
a primarykey, you can use, as example:

"primaryKey=" & wantedPrimaryKey


for this argument, in the DoCmd.OpenForm. I assumed the field name is
primaryKey and your variable wantedPrimaryKey contains the numerical value
for the desired primary key.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi Michel,

Thanks very much for you reply. I am however, a bit stuck as I don't quite
know where to start. Should I create a form with the combo box first which
would hold the primary key 'studyno' and the title of the study 'studyname'.
When I click on the studyno I want it to take me to the form where I enter
the details of the study 'study details' and for that specific record to be
shown (that I'd just selected).
I'm not sure what or where to put the coding that you have given to me -
sorry about this. Would it be possible for you give me a step by step guide
as to where to go and what to click, so I can incorporate the coding to
achieve the above. This would be such a great help to me and I'd be really
pleased.
Kind regards,
Liz.

 

[Michel Walsh]

Hi,



Your combo box has two columns? then in the AfterUpdate event (subroutine
handler) of the combo box, should be:


DoCmd.OpenForm "FormNameToOpen",
WhereCondition:="PrimaryKeyFieldNameHere=FORMS!ActualFormNameHere!ActualComboBoxControlNameHere"



should do. FormNameToOpen, PrimaryKeyFieldNameHere and ActualFormNameHere
and also ActualComboBoxControlNameHere has to be replaced with the actual
name you use. Note there is a : in front of the = right after
WhereCondition (to be written as it is). That syntax allows to skip over
some optional parameters, making the line a shorter statement (and less
confusing than counting the right number of coma ) .



I suggest John L. Viescas book, "Microsoft Access 2003 Inside Out", as
example, if you look for a book targeting the basic user interface.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Michel,

I'm still having trouble and hope you don't get fed up with me as I'd really
like to get this control to work as it will be so useful for future databases
too which I do.
I put:

Private Sub Combo25_AfterUpdate()
DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS!Start up form!Combo25"

End Sub

However, I keep getting errors in yellow and red highlights and the :=
becomes highlighted too. Can you spot where I could be going wrong?

Kind regards,
Liz.

 

[Michel Walsh]

Hi



Quite likely the illegal name for the form name, since it contains spaces.
In such case, you have to surround it with [ ]:


DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS![Start up form]!Combo25"



be sure the [ ] does NOT include any of the bangs (!).



Hoping it may help
Vanderghast, Access MVP

 

[Guest]

Michel,

I'm back again - hoping again that you will stay with me until I can get
this right! I put:

Private Sub Combo25_AfterUpdate()
DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS![Start up form]!Combo25"

End Sub

When the code is run, it says complie error and syntax error. The first line
is higlighted in yellow, the second line in blue and the 3rd line is in red.
Do you think I could be doing something wrong when I set up my combo box with
the wizard? When it asks where I want to store the StudyRefNo, I choose the
option, store for later use, as I don't want to store it in the StaffIDNo on
that form (Start up form). The combo box itself looks fine - it has the
primary field in and the title of the study. Maybe I am missing a comma or
something in my coding?

Thanks again,
Liz.

Hi



Quite likely the illegal name for the form name, since it contains spaces.
In such case, you have to surround it with [ ]:


DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS![Start up form]!Combo25"



be sure the [ ] does NOT include any of the bangs (!).



Hoping it may help
Vanderghast, Access MVP

Michel,

I'm still having trouble and hope you don't get fed up with me as I'd
really
like to get this control to work as it will be so useful for future
databases
too which I do.
I put:

Private Sub Combo25_AfterUpdate()
DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS!Start up form!Combo25"

End Sub

However, I keep getting errors in yellow and red highlights and the :=
becomes highlighted too. Can you spot where I could be going wrong?

Kind regards,
Liz.

 

[Michel Walsh]

Hi,


Sounds like you have two lines where there should be just one.


The code that starts with DoCmd ends with Combo25", on the same line.
The news reader probably make it appears as TWO lines, but it should be just
one, in reality.


The whole relevant code should thus have 3 not-empty lines: one starts with
Private, one starts with DoCmd, one starts with End Sub.


Hoping it may help
Vanderghast, Access MVP

Michel,

I'm back again - hoping again that you will stay with me until I can get
this right! I put:

Private Sub Combo25_AfterUpdate()
DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS![Start up form]!Combo25"

End Sub

When the code is run, it says complie error and syntax error. The first
line
is higlighted in yellow, the second line in blue and the 3rd line is in
red.
Do you think I could be doing something wrong when I set up my combo box
with
the wizard? When it asks where I want to store the StudyRefNo, I choose
the
option, store for later use, as I don't want to store it in the StaffIDNo
on
that form (Start up form). The combo box itself looks fine - it has the
primary field in and the title of the study. Maybe I am missing a comma
or
something in my coding?

Thanks again,
Liz.

Hi



Quite likely the illegal name for the form name, since it contains
spaces.
In such case, you have to surround it with [ ]:


DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS![Start up form]!Combo25"



be sure the [ ] does NOT include any of the bangs (!).



Hoping it may help
Vanderghast, Access MVP


message

Michel,

I'm still having trouble and hope you don't get fed up with me as I'd
really
like to get this control to work as it will be so useful for future
databases
too which I do.
I put:

Private Sub Combo25_AfterUpdate()
DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS!Start up form!Combo25"

End Sub

However, I keep getting errors in yellow and red highlights and the :=
becomes highlighted too. Can you spot where I could be going wrong?

Kind regards,
Liz.

:

Hi,



Your combo box has two columns? then in the AfterUpdate event
(subroutine
handler) of the combo box, should be:


DoCmd.OpenForm "FormNameToOpen",
WhereCondition:="PrimaryKeyFieldNameHere=FORMS!ActualFormNameHere!ActualComboBoxControlNameHere"



should do. FormNameToOpen, PrimaryKeyFieldNameHere and
ActualFormNameHere
and also ActualComboBoxControlNameHere has to be replaced with the
actual
name you use. Note there is a : in front of the = right after
WhereCondition (to be written as it is). That syntax allows to skip
over
some optional parameters, making the line a shorter statement (and
less
confusing than counting the right number of coma ) .



I suggest John L. Viescas book, "Microsoft Access 2003 Inside Out", as
example, if you look for a book targeting the basic user interface.



Hoping it may help,
Vanderghast, Access MVP




message
Hi Michel,

Thanks very much for you reply. I am however, a bit stuck as I don't
quite
know where to start. Should I create a form with the combo box first
which
would hold the primary key 'studyno' and the title of the study
'studyname'.
When I click on the studyno I want it to take me to the form where I
enter
the details of the study 'study details' and for that specific
record
to
be
shown (that I'd just selected).
I'm not sure what or where to put the coding that you have given to
me -
sorry about this. Would it be possible for you give me a step by
step
guide
as to where to go and what to click, so I can incorporate the coding
to
achieve the above. This would be such a great help to me and I'd be
really
pleased.
Kind regards,
Liz.

:

Hi,


The DoCmd.OpenForm procedure allow a parameter, whereCondition, to
specify,
as a string, a condition that limit the records to be displayed. If
you
have
a primarykey, you can use, as example:

"primaryKey=" & wantedPrimaryKey


for this argument, in the DoCmd.OpenForm. I assumed the field name
is
primaryKey and your variable wantedPrimaryKey contains the
numerical
value
for the desired primary key.


Hoping it may help,
Vanderghast, Access MVP


message
Hi,

I am still working on an Access database which holds records of
clinical
trials studies. Each study has its own study number and title.
When
the
database starts up, the switchboard form appears first. I would
like
on
here,
to be able to select from a drop down list the a certain record
and
after
clicking the title, study number or both, the form where I enter
details
for
these records/studies will view the record just selected from the
list
box/drop down list I want on my switch board form. Can anyone
describe
a
non-complicated or step by step guide on how I could create this,
as
I
do
most of my form features e.g command buttons, using the wizard
toolbar.
I'd
be most grateful.
Many thanks,
Liz.

 

[Guest]

Hi Michel,

My combo box works a treat now! Thank you very much. I am really happy - you
have been brilliant.

Best regards,

Liz.

Hi,


Sounds like you have two lines where there should be just one.


The code that starts with DoCmd ends with Combo25", on the same line.
The news reader probably make it appears as TWO lines, but it should be just
one, in reality.


The whole relevant code should thus have 3 not-empty lines: one starts with
Private, one starts with DoCmd, one starts with End Sub.


Hoping it may help
Vanderghast, Access MVP

Michel,

I'm back again - hoping again that you will stay with me until I can get
this right! I put:

Private Sub Combo25_AfterUpdate()
DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS![Start up form]!Combo25"

End Sub

When the code is run, it says complie error and syntax error. The first
line
is higlighted in yellow, the second line in blue and the 3rd line is in
red.
Do you think I could be doing something wrong when I set up my combo box
with
the wizard? When it asks where I want to store the StudyRefNo, I choose
the
option, store for later use, as I don't want to store it in the StaffIDNo
on
that form (Start up form). The combo box itself looks fine - it has the
primary field in and the title of the study. Maybe I am missing a comma
or
something in my coding?

Thanks again,
Liz.

Hi



Quite likely the illegal name for the form name, since it contains
spaces.
In such case, you have to surround it with [ ]:


DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS![Start up form]!Combo25"



be sure the [ ] does NOT include any of the bangs (!).



Hoping it may help
Vanderghast, Access MVP


message
Michel,

I'm still having trouble and hope you don't get fed up with me as I'd
really
like to get this control to work as it will be so useful for future
databases
too which I do.
I put:

Private Sub Combo25_AfterUpdate()
DoCmd.OpenForm "Study details1",
WhereCondition:="StudyRefNo=FORMS!Start up form!Combo25"

End Sub

However, I keep getting errors in yellow and red highlights and the :=
becomes highlighted too. Can you spot where I could be going wrong?

Kind regards,
Liz.

:

Hi,



Your combo box has two columns? then in the AfterUpdate event
(subroutine
handler) of the combo box, should be:


DoCmd.OpenForm "FormNameToOpen",
WhereCondition:="PrimaryKeyFieldNameHere=FORMS!ActualFormNameHere!ActualComboBoxControlNameHere"



should do. FormNameToOpen, PrimaryKeyFieldNameHere and
ActualFormNameHere
and also ActualComboBoxControlNameHere has to be replaced with the
actual
name you use. Note there is a : in front of the = right after
WhereCondition (to be written as it is). That syntax allows to skip
over
some optional parameters, making the line a shorter statement (and
less
confusing than counting the right number of coma ) .



I suggest John L. Viescas book, "Microsoft Access 2003 Inside Out", as
example, if you look for a book targeting the basic user interface.



Hoping it may help,
Vanderghast, Access MVP




message
Hi Michel,

Thanks very much for you reply. I am however, a bit stuck as I don't
quite
know where to start. Should I create a form with the combo box first
which
would hold the primary key 'studyno' and the title of the study
'studyname'.
When I click on the studyno I want it to take me to the form where I
enter
the details of the study 'study details' and for that specific
record
to
be
shown (that I'd just selected).
I'm not sure what or where to put the coding that you have given to
me -
sorry about this. Would it be possible for you give me a step by
step
guide
as to where to go and what to click, so I can incorporate the coding
to
achieve the above. This would be such a great help to me and I'd be
really
pleased.
Kind regards,
Liz.

:

Hi,


The DoCmd.OpenForm procedure allow a parameter, whereCondition, to
specify,
as a string, a condition that limit the records to be displayed. If
you
have
a primarykey, you can use, as example:

"primaryKey=" & wantedPrimaryKey


for this argument, in the DoCmd.OpenForm. I assumed the field name
is
primaryKey and your variable wantedPrimaryKey contains the
numerical
value
for the desired primary key.


Hoping it may help,
Vanderghast, Access MVP


message
Hi,

I am still working on an Access database which holds records of
clinical
trials studies. Each study has its own study number and title.
When
the
database starts up, the switchboard form appears first. I would
like
on
here,
to be able to select from a drop down list the a certain record
and
after
clicking the title, study number or both, the form where I enter
details
for
these records/studies will view the record just selected from the
list
box/drop down list I want on my switch board form. Can anyone
describe
a
non-complicated or step by step guide on how I could create this,
as
I
do
most of my form features e.g command buttons, using the wizard
toolbar.
I'd
be most grateful.
Many thanks,
Liz.