Using a Form to open 2nd form

[Guest]

Hello,

I have created a form using a query. The query has a field that selects
criteria to open the first form such as a Birthdate. I also added a combo
field (studentname) in the first form. That way I can make sure that the
Birthdate matches up with the student name. Once that is correct, I added a
command button on the first form to go to the 2nd form for them to view the
infomation that was selected from the first form. That seems to work fine.
Now here is where I need your help.

1) When I close the second form (I use a command button) It takes me back to
the first form which is okay but I need it to be cleared from what was there
from the beginning. I need the field to be blank to start all over again.

2)When the 2nd forms opens, I would like to have a verification field to be
checked before the form closes. The second form will not close until it is
checked off. Will I need to have a third form for Verification and then close
from there?

3)Is there some way for me to know if a field has been edited or changed? I
want to keep track of all the changes.

4) Once everything has been edited and viewed, is there a way for me to save
all the information on the second form to a new table? I want to create a new
table once all the information has been editied and verified.

Thanks,

Robin

 

[Allen Browne]

Answers in-line. The key to the things you asked for is knowing how to use
the events that Access triggers when different things happen in your form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have created a form using a query. The query has a field that selects
criteria to open the first form such as a Birthdate. I also added a
combo
field (studentname) in the first form. That way I can make sure that the
Birthdate matches up with the student name. Once that is correct, I added
a
command button on the first form to go to the 2nd form for them to view
the
infomation that was selected from the first form. That seems to work
fine.
Now here is where I need your help.

1) When I close the second form (I use a command button) It takes me back
to
the first form which is okay but I need it to be cleared from what was
there
from the beginning. I need the field to be blank to start all over again.

You can use the Close event of form2 to clear the controls on form1. Use a
macro with the SetValue action, e.g. set:
[Forms].[Form1].[txt2Find]
to
Null
(This assumes that the text box txt2Find is unbound.)

2)When the 2nd forms opens, I would like to have a verification field to
be
checked before the form closes. The second form will not close until it
is
checked off. Will I need to have a third form for Verification and then
close
from there?

Use the BeforeUpdate event of form2 to perform the validation.

This is an example of an Event Procedure that prevents the record being
saved until the Verification check box is checked:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.Verification.Value Then
Cancel = True
MsgBox "Verification must be checked before the record can be
saved."
End If
End Sub

3)Is there some way for me to know if a field has been edited or changed?
I
want to keep track of all the changes.

Access does not provide a way to log the inserts, edits, and deletions, but
you can achieve the results by using the events of the form.

You will need to be familiar with VBA code to achieve this. Details in:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

4) Once everything has been edited and viewed, is there a way for me to
save
all the information on the second form to a new table? I want to create a
new
table once all the information has been editied and verified.

You can use the AfterUpdate event of the form to execute an append query
statement that makes a copy of the record into another table.

 

[Guest]

Hello,

If you don't mind helping me a little more in getting through Step #1 where
I close the second form and it returns back to the first form.

Here is what I did according to your information that you sent. Maybe I did
not follow it right.

I went into the properties of the 2nd form and then click on Close event to
build the Macro. The Action I selected SetValue and then where it said Item,
I have the following: [Forms]![Student Registration 04262006]![StudentName]

In the Express blank I enter: [StudentName]

When I close the 2nd form by using the close command button, I receive a
error message on the Macro that was built "Action Failed" Where do I insert
"to Null" that you indicated to me to use. I tried adding it at the end on
the Item section but received errors.

Please help. I do not want to continue on with other steps until I fix step
1.

Thanks,

Robin

Answers in-line. The key to the things you asked for is knowing how to use
the events that Access triggers when different things happen in your form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have created a form using a query. The query has a field that selects
criteria to open the first form such as a Birthdate. I also added a
combo
field (studentname) in the first form. That way I can make sure that the
Birthdate matches up with the student name. Once that is correct, I added
a
command button on the first form to go to the 2nd form for them to view
the
infomation that was selected from the first form. That seems to work
fine.
Now here is where I need your help.

1) When I close the second form (I use a command button) It takes me back
to
the first form which is okay but I need it to be cleared from what was
there
from the beginning. I need the field to be blank to start all over again.

You can use the Close event of form2 to clear the controls on form1. Use a
macro with the SetValue action, e.g. set:
[Forms].[Form1].[txt2Find]
to
Null
(This assumes that the text box txt2Find is unbound.)

2)When the 2nd forms opens, I would like to have a verification field to
be
checked before the form closes. The second form will not close until it
is
checked off. Will I need to have a third form for Verification and then
close
from there?

Use the BeforeUpdate event of form2 to perform the validation.

This is an example of an Event Procedure that prevents the record being
saved until the Verification check box is checked:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.Verification.Value Then
Cancel = True
MsgBox "Verification must be checked before the record can be
saved."
End If
End Sub

3)Is there some way for me to know if a field has been edited or changed?
I
want to keep track of all the changes.

Access does not provide a way to log the inserts, edits, and deletions, but
you can achieve the results by using the events of the form.

You will need to be familiar with VBA code to achieve this. Details in:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

4) Once everything has been edited and viewed, is there a way for me to
save
all the information on the second form to a new table? I want to create a
new
table once all the information has been editied and verified.

You can use the AfterUpdate event of the form to execute an append query
statement that makes a copy of the record into another table.

 

[Allen Browne]

To use the SetValue action in a macro to blank out the StudentName text box
on form named "Student Registration 04262006", your reference looks correct
for the Item. For the Expression, just enter:
Null

If you cannot get that to work, you could set the On Close property to:
[Event Procedure]
and click the Build button (...) beside this.
Access opens a code window.
Between the "Private Sub..." and "End Sub" lines, enter:
Forms![Student Registration 04262006]!StudentName = Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

If you don't mind helping me a little more in getting through Step #1
where
I close the second form and it returns back to the first form.

Here is what I did according to your information that you sent. Maybe I
did
not follow it right.

I went into the properties of the 2nd form and then click on Close event
to
build the Macro. The Action I selected SetValue and then where it said
Item,
I have the following: [Forms]![Student Registration
04262006]![StudentName]

In the Express blank I enter: [StudentName]

When I close the 2nd form by using the close command button, I receive a
error message on the Macro that was built "Action Failed" Where do I
insert
"to Null" that you indicated to me to use. I tried adding it at the end
on
the Item section but received errors.

Please help. I do not want to continue on with other steps until I fix
step
1.

Thanks,

Robin

Answers in-line. The key to the things you asked for is knowing how to
use
the events that Access triggers when different things happen in your
form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have created a form using a query. The query has a field that
selects
criteria to open the first form such as a Birthdate. I also added a
combo
field (studentname) in the first form. That way I can make sure that
the
Birthdate matches up with the student name. Once that is correct, I
added
a
command button on the first form to go to the 2nd form for them to view
the
infomation that was selected from the first form. That seems to work
fine.
Now here is where I need your help.

1) When I close the second form (I use a command button) It takes me
back
to
the first form which is okay but I need it to be cleared from what was
there
from the beginning. I need the field to be blank to start all over
again.

You can use the Close event of form2 to clear the controls on form1. Use
a
macro with the SetValue action, e.g. set:
[Forms].[Form1].[txt2Find]
to
Null
(This assumes that the text box txt2Find is unbound.)

2)When the 2nd forms opens, I would like to have a verification field
to
be
checked before the form closes. The second form will not close until
it
is
checked off. Will I need to have a third form for Verification and then
close
from there?

Use the BeforeUpdate event of form2 to perform the validation.

This is an example of an Event Procedure that prevents the record being
saved until the Verification check box is checked:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.Verification.Value Then
Cancel = True
MsgBox "Verification must be checked before the record can be
saved."
End If
End Sub

3)Is there some way for me to know if a field has been edited or
changed?
I
want to keep track of all the changes.

Access does not provide a way to log the inserts, edits, and deletions,
but
you can achieve the results by using the events of the form.

You will need to be familiar with VBA code to achieve this. Details in:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

4) Once everything has been edited and viewed, is there a way for me to
save
all the information on the second form to a new table? I want to create
a
new
table once all the information has been editied and verified.

You can use the AfterUpdate event of the form to execute an append query
statement that makes a copy of the record into another table.

 

[Guest]

Hello,

I think by adding the form information in the Event procedure works but I
have one problem. My query has "grouped by" to allow me to use the critera
to select the record that I want to show up in the second form. I am
requesting Birthdate to be entered to pull the students record. When I close
the 2nd form I receive an error msg " Recordset is not updateable" Is this
because I have grouped by in my query? I can I correct this or is there a
better way to do what I want.

Thanks,

Robin

To use the SetValue action in a macro to blank out the StudentName text box
on form named "Student Registration 04262006", your reference looks correct
for the Item. For the Expression, just enter:
Null

If you cannot get that to work, you could set the On Close property to:
[Event Procedure]
and click the Build button (...) beside this.
Access opens a code window.
Between the "Private Sub..." and "End Sub" lines, enter:
Forms![Student Registration 04262006]!StudentName = Null

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

If you don't mind helping me a little more in getting through Step #1
where
I close the second form and it returns back to the first form.

Here is what I did according to your information that you sent. Maybe I
did
not follow it right.

I went into the properties of the 2nd form and then click on Close event
to
build the Macro. The Action I selected SetValue and then where it said
Item,
I have the following: [Forms]![Student Registration
04262006]![StudentName]

In the Express blank I enter: [StudentName]

When I close the 2nd form by using the close command button, I receive a
error message on the Macro that was built "Action Failed" Where do I
insert
"to Null" that you indicated to me to use. I tried adding it at the end
on
the Item section but received errors.

Please help. I do not want to continue on with other steps until I fix
step
1.

Thanks,

Robin

Answers in-line. The key to the things you asked for is knowing how to
use
the events that Access triggers when different things happen in your
form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I have created a form using a query. The query has a field that
selects
criteria to open the first form such as a Birthdate. I also added a
combo
field (studentname) in the first form. That way I can make sure that
the
Birthdate matches up with the student name. Once that is correct, I
added
a
command button on the first form to go to the 2nd form for them to view
the
infomation that was selected from the first form. That seems to work
fine.
Now here is where I need your help.

1) When I close the second form (I use a command button) It takes me
back
to
the first form which is okay but I need it to be cleared from what was
there
from the beginning. I need the field to be blank to start all over
again.

You can use the Close event of form2 to clear the controls on form1. Use
a
macro with the SetValue action, e.g. set:
[Forms].[Form1].[txt2Find]
to
Null
(This assumes that the text box txt2Find is unbound.)


2)When the 2nd forms opens, I would like to have a verification field
to
be
checked before the form closes. The second form will not close until
it
is
checked off. Will I need to have a third form for Verification and then
close
from there?

Use the BeforeUpdate event of form2 to perform the validation.

This is an example of an Event Procedure that prevents the record being
saved until the Verification check box is checked:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.Verification.Value Then
Cancel = True
MsgBox "Verification must be checked before the record can be
saved."
End If
End Sub


3)Is there some way for me to know if a field has been edited or
changed?
I
want to keep track of all the changes.

Access does not provide a way to log the inserts, edits, and deletions,
but
you can achieve the results by using the events of the form.

You will need to be familiar with VBA code to achieve this. Details in:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

4) Once everything has been edited and viewed, is there a way for me to
save
all the information on the second form to a new table? I want to create
a
new
table once all the information has been editied and verified.

You can use the AfterUpdate event of the form to execute an append query
statement that makes a copy of the record into another table.

 

[Allen Browne]

Yes. Using a GROUP BY clause in your query would be enough to make it not
updatable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I think by adding the form information in the Event procedure works but I
have one problem. My query has "grouped by" to allow me to use the
critera
to select the record that I want to show up in the second form. I am
requesting Birthdate to be entered to pull the students record. When I
close
the 2nd form I receive an error msg " Recordset is not updateable" Is
this
because I have grouped by in my query? I can I correct this or is there a
better way to do what I want.

Thanks,

Robin

To use the SetValue action in a macro to blank out the StudentName text
box
on form named "Student Registration 04262006", your reference looks
correct
for the Item. For the Expression, just enter:
Null

If you cannot get that to work, you could set the On Close property to:
[Event Procedure]
and click the Build button (...) beside this.
Access opens a code window.
Between the "Private Sub..." and "End Sub" lines, enter:
Forms![Student Registration 04262006]!StudentName = Null

Hello,

If you don't mind helping me a little more in getting through Step #1
where
I close the second form and it returns back to the first form.

Here is what I did according to your information that you sent. Maybe
I
did
not follow it right.

I went into the properties of the 2nd form and then click on Close
event
to
build the Macro. The Action I selected SetValue and then where it said
Item,
I have the following: [Forms]![Student Registration
04262006]![StudentName]

In the Express blank I enter: [StudentName]

When I close the 2nd form by using the close command button, I receive
a
error message on the Macro that was built "Action Failed" Where do I
insert
"to Null" that you indicated to me to use. I tried adding it at the
end
on
the Item section but received errors.

Please help. I do not want to continue on with other steps until I fix
step
1.

Thanks,

Robin

:

Answers in-line. The key to the things you asked for is knowing how to
use
the events that Access triggers when different things happen in your
form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I have created a form using a query. The query has a field that
selects
criteria to open the first form such as a Birthdate. I also added
a
combo
field (studentname) in the first form. That way I can make sure
that
the
Birthdate matches up with the student name. Once that is correct, I
added
a
command button on the first form to go to the 2nd form for them to
view
the
infomation that was selected from the first form. That seems to
work
fine.
Now here is where I need your help.

1) When I close the second form (I use a command button) It takes me
back
to
the first form which is okay but I need it to be cleared from what
was
there
from the beginning. I need the field to be blank to start all over
again.

You can use the Close event of form2 to clear the controls on form1.
Use
a
macro with the SetValue action, e.g. set:
[Forms].[Form1].[txt2Find]
to
Null
(This assumes that the text box txt2Find is unbound.)


2)When the 2nd forms opens, I would like to have a verification
field
to
be
checked before the form closes. The second form will not close
until
it
is
checked off. Will I need to have a third form for Verification and
then
close
from there?

Use the BeforeUpdate event of form2 to perform the validation.

This is an example of an Event Procedure that prevents the record
being
saved until the Verification check box is checked:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.Verification.Value Then
Cancel = True
MsgBox "Verification must be checked before the record can
be
saved."
End If
End Sub


3)Is there some way for me to know if a field has been edited or
changed?
I
want to keep track of all the changes.

Access does not provide a way to log the inserts, edits, and
deletions,
but
you can achieve the results by using the events of the form.

You will need to be familiar with VBA code to achieve this. Details
in:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

4) Once everything has been edited and viewed, is there a way for me
to
save
all the information on the second form to a new table? I want to
create
a
new
table once all the information has been editied and verified.

You can use the AfterUpdate event of the form to execute an append
query
statement that makes a copy of the record into another table.

 

[Guest]

Thanks for your reply but could you please tell me how to correct this. Any
help would be appreciated

Thanks,

Robin

Yes. Using a GROUP BY clause in your query would be enough to make it not
updatable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I think by adding the form information in the Event procedure works but I
have one problem. My query has "grouped by" to allow me to use the
critera
to select the record that I want to show up in the second form. I am
requesting Birthdate to be entered to pull the students record. When I
close
the 2nd form I receive an error msg " Recordset is not updateable" Is
this
because I have grouped by in my query? I can I correct this or is there a
better way to do what I want.

Thanks,

Robin

To use the SetValue action in a macro to blank out the StudentName text
box
on form named "Student Registration 04262006", your reference looks
correct
for the Item. For the Expression, just enter:
Null

If you cannot get that to work, you could set the On Close property to:
[Event Procedure]
and click the Build button (...) beside this.
Access opens a code window.
Between the "Private Sub..." and "End Sub" lines, enter:
Forms![Student Registration 04262006]!StudentName = Null

Hello,

If you don't mind helping me a little more in getting through Step #1
where
I close the second form and it returns back to the first form.

Here is what I did according to your information that you sent. Maybe
I
did
not follow it right.

I went into the properties of the 2nd form and then click on Close
event
to
build the Macro. The Action I selected SetValue and then where it said
Item,
I have the following: [Forms]![Student Registration
04262006]![StudentName]

In the Express blank I enter: [StudentName]

When I close the 2nd form by using the close command button, I receive
a
error message on the Macro that was built "Action Failed" Where do I
insert
"to Null" that you indicated to me to use. I tried adding it at the
end
on
the Item section but received errors.

Please help. I do not want to continue on with other steps until I fix
step
1.

Thanks,

Robin

:

Answers in-line. The key to the things you asked for is knowing how to
use
the events that Access triggers when different things happen in your
form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I have created a form using a query. The query has a field that
selects
criteria to open the first form such as a Birthdate. I also added
a
combo
field (studentname) in the first form. That way I can make sure
that
the
Birthdate matches up with the student name. Once that is correct, I
added
a
command button on the first form to go to the 2nd form for them to
view
the
infomation that was selected from the first form. That seems to
work
fine.
Now here is where I need your help.

1) When I close the second form (I use a command button) It takes me
back
to
the first form which is okay but I need it to be cleared from what
was
there
from the beginning. I need the field to be blank to start all over
again.

You can use the Close event of form2 to clear the controls on form1.
Use
a
macro with the SetValue action, e.g. set:
[Forms].[Form1].[txt2Find]
to
Null
(This assumes that the text box txt2Find is unbound.)


2)When the 2nd forms opens, I would like to have a verification
field
to
be
checked before the form closes. The second form will not close
until
it
is
checked off. Will I need to have a third form for Verification and
then
close
from there?

Use the BeforeUpdate event of form2 to perform the validation.

This is an example of an Event Procedure that prevents the record
being
saved until the Verification check box is checked:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.Verification.Value Then
Cancel = True
MsgBox "Verification must be checked before the record can
be
saved."
End If
End Sub


3)Is there some way for me to know if a field has been edited or
changed?
I
want to keep track of all the changes.

Access does not provide a way to log the inserts, edits, and
deletions,
but
you can achieve the results by using the events of the form.

You will need to be familiar with VBA code to achieve this. Details
in:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

4) Once everything has been edited and viewed, is there a way for me
to
save
all the information on the second form to a new table? I want to
create
a
new
table once all the information has been editied and verified.

You can use the AfterUpdate event of the form to execute an append
query
statement that makes a copy of the record into another table.

 

[Guest]

Okay, I think I figured out the GROUP BY clause. I changed everything in
the query to Expression and took out the criteria where I had it asking the
the person to enter a Date of Birth. Now the Form has Two Combo boxes. Now
my question is how do I link the two combo boxes together so as to when I
click on the 2nd form It will pull the matching data. Right now It links the
Student Name to the 2nd form regardless of what I put in for the birthdate.
When I use the wizard for the command button to goto the 2nd form It only
allows me to link one field not two. How can I link both fields?

Thanks,

Robin

Yes. Using a GROUP BY clause in your query would be enough to make it not
updatable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I think by adding the form information in the Event procedure works but I
have one problem. My query has "grouped by" to allow me to use the
critera
to select the record that I want to show up in the second form. I am
requesting Birthdate to be entered to pull the students record. When I
close
the 2nd form I receive an error msg " Recordset is not updateable" Is
this
because I have grouped by in my query? I can I correct this or is there a
better way to do what I want.

Thanks,

Robin

To use the SetValue action in a macro to blank out the StudentName text
box
on form named "Student Registration 04262006", your reference looks
correct
for the Item. For the Expression, just enter:
Null

If you cannot get that to work, you could set the On Close property to:
[Event Procedure]
and click the Build button (...) beside this.
Access opens a code window.
Between the "Private Sub..." and "End Sub" lines, enter:
Forms![Student Registration 04262006]!StudentName = Null

Hello,

If you don't mind helping me a little more in getting through Step #1
where
I close the second form and it returns back to the first form.

Here is what I did according to your information that you sent. Maybe
I
did
not follow it right.

I went into the properties of the 2nd form and then click on Close
event
to
build the Macro. The Action I selected SetValue and then where it said
Item,
I have the following: [Forms]![Student Registration
04262006]![StudentName]

In the Express blank I enter: [StudentName]

When I close the 2nd form by using the close command button, I receive
a
error message on the Macro that was built "Action Failed" Where do I
insert
"to Null" that you indicated to me to use. I tried adding it at the
end
on
the Item section but received errors.

Please help. I do not want to continue on with other steps until I fix
step
1.

Thanks,

Robin

:

Answers in-line. The key to the things you asked for is knowing how to
use
the events that Access triggers when different things happen in your
form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


I have created a form using a query. The query has a field that
selects
criteria to open the first form such as a Birthdate. I also added
a
combo
field (studentname) in the first form. That way I can make sure
that
the
Birthdate matches up with the student name. Once that is correct, I
added
a
command button on the first form to go to the 2nd form for them to
view
the
infomation that was selected from the first form. That seems to
work
fine.
Now here is where I need your help.

1) When I close the second form (I use a command button) It takes me
back
to
the first form which is okay but I need it to be cleared from what
was
there
from the beginning. I need the field to be blank to start all over
again.

You can use the Close event of form2 to clear the controls on form1.
Use
a
macro with the SetValue action, e.g. set:
[Forms].[Form1].[txt2Find]
to
Null
(This assumes that the text box txt2Find is unbound.)


2)When the 2nd forms opens, I would like to have a verification
field
to
be
checked before the form closes. The second form will not close
until
it
is
checked off. Will I need to have a third form for Verification and
then
close
from there?

Use the BeforeUpdate event of form2 to perform the validation.

This is an example of an Event Procedure that prevents the record
being
saved until the Verification check box is checked:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me.Verification.Value Then
Cancel = True
MsgBox "Verification must be checked before the record can
be
saved."
End If
End Sub


3)Is there some way for me to know if a field has been edited or
changed?
I
want to keep track of all the changes.

Access does not provide a way to log the inserts, edits, and
deletions,
but
you can achieve the results by using the events of the form.

You will need to be familiar with VBA code to achieve this. Details
in:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html

4) Once everything has been edited and viewed, is there a way for me
to
save
all the information on the second form to a new table? I want to
create
a
new
table once all the information has been editied and verified.

You can use the AfterUpdate event of the form to execute an append
query
statement that makes a copy of the record into another table.

 

[Allen Browne]

Link on the primary key field, and you cannot go wrong.

If you want to link on the 2 fields, you could change the link criteria
string to something like this:
stLinkCriteria = "(lastname = """ & Me.lastname & _
""") AND (firstname = """ & Me.firstname & """)"

 

[Guest]

I want to thank you for your help. I was trying to use the following
statement in the property section in the click event of the command button on
my form, but I am receiving this error. "Type mismatch"

Could use take a look at the code that I put in to see where I went wrong.
We were trying to link two combo boxes together so that that record would
open on the 2nd form.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Student Registration 04232006"


stLinkCriteria = "[StudentName]=" & "'" & Me![Combo8] & "'" And "[DOB]="
& "#" & Me![Combo2] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub


Thanks,

Robin

 

[Allen Browne]

The "Type mismatch" message indicates Access thinks you are comparing 2
different kinds of things, such as comparing a number to a name.

Look at the properties of Combo8. The RowSource shows which columns it has,
and the bound column tells you which one is the value of the combo. The
Column Widths property might make the bound column zero-width, in which case
you do not see the real value. If the Bound Column refers to a Number type
field, you have a type mismatch.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to thank you for your help. I was trying to use the following
statement in the property section in the click event of the command button
on
my form, but I am receiving this error. "Type mismatch"

Could use take a look at the code that I put in to see where I went wrong.
We were trying to link two combo boxes together so that that record would
open on the 2nd form.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Student Registration 04232006"


stLinkCriteria = "[StudentName]=" & "'" & Me![Combo8] & "'" And
"[DOB]="
& "#" & Me![Combo2] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub


Thanks,

Robin

Link on the primary key field, and you cannot go wrong.

If you want to link on the 2 fields, you could change the link criteria
string to something like this:
stLinkCriteria = "(lastname = """ & Me.lastname & _
""") AND (firstname = """ & Me.firstname & """)"

 

[Guest]

Sorry to trouble you, Let see if I can get it right this time.

(Combo2) is numeric ; Row/source - DOB; bound 1; col width 1; This field
is were I enter a birthdate (01/01/2002)

(Combo8) is text; Row/Source - StudentName; Bound 1; Column Width 1.5; This
field is were I enter the student name such as "Doe, John".

You indicated that a bound field that is a numeric will have a type
mismatch. The Combo2 is numeric. I need to type the student name and then
enter the birthdate so that the next form will pull the right record. How
can I fix the combo field?

Thanks,

Robin

The "Type mismatch" message indicates Access thinks you are comparing 2
different kinds of things, such as comparing a number to a name.

Look at the properties of Combo8. The RowSource shows which columns it has,
and the bound column tells you which one is the value of the combo. The
Column Widths property might make the bound column zero-width, in which case
you do not see the real value. If the Bound Column refers to a Number type
field, you have a type mismatch.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to thank you for your help. I was trying to use the following
statement in the property section in the click event of the command button
on
my form, but I am receiving this error. "Type mismatch"

Could use take a look at the code that I put in to see where I went wrong.
We were trying to link two combo boxes together so that that record would
open on the 2nd form.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Student Registration 04232006"


stLinkCriteria = "[StudentName]=" & "'" & Me![Combo8] & "'" And
"[DOB]="
& "#" & Me![Combo2] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub


Thanks,

Robin

Link on the primary key field, and you cannot go wrong.

If you want to link on the 2 fields, you could change the link criteria
string to something like this:
stLinkCriteria = "(lastname = """ & Me.lastname & _
""") AND (firstname = """ & Me.firstname & """)"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Okay, I think I figured out the GROUP BY clause. I changed everything
in
the query to Expression and took out the criteria where I had it asking
the
the person to enter a Date of Birth. Now the Form has Two Combo boxes.
Now
my question is how do I link the two combo boxes together so as to when
I
click on the 2nd form It will pull the matching data. Right now It
links
the
Student Name to the 2nd form regardless of what I put in for the
birthdate.
When I use the wizard for the command button to goto the 2nd form It
only
allows me to link one field not two. How can I link both fields?

 

[Allen Browne]

The issue is not that the combo is a number, but that the types are not the
same.

You say Combo2 is numeric? But you are entering a date there? If it is
unbound, set its Format property to Short Date, so Access understands it is
a date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sorry to trouble you, Let see if I can get it right this time.

(Combo2) is numeric ; Row/source - DOB; bound 1; col width 1; This
field
is were I enter a birthdate (01/01/2002)

(Combo8) is text; Row/Source - StudentName; Bound 1; Column Width 1.5;
This
field is were I enter the student name such as "Doe, John".

You indicated that a bound field that is a numeric will have a type
mismatch. The Combo2 is numeric. I need to type the student name and
then
enter the birthdate so that the next form will pull the right record. How
can I fix the combo field?

Thanks,

Robin

The "Type mismatch" message indicates Access thinks you are comparing 2
different kinds of things, such as comparing a number to a name.

Look at the properties of Combo8. The RowSource shows which columns it
has,
and the bound column tells you which one is the value of the combo. The
Column Widths property might make the bound column zero-width, in which
case
you do not see the real value. If the Bound Column refers to a Number
type
field, you have a type mismatch.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to thank you for your help. I was trying to use the following
statement in the property section in the click event of the command
button
on
my form, but I am receiving this error. "Type mismatch"

Could use take a look at the code that I put in to see where I went
wrong.
We were trying to link two combo boxes together so that that record
would
open on the 2nd form.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Student Registration 04232006"


stLinkCriteria = "[StudentName]=" & "'" & Me![Combo8] & "'" And
"[DOB]="
& "#" & Me![Combo2] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub


Thanks,

Robin


:

Link on the primary key field, and you cannot go wrong.

If you want to link on the 2 fields, you could change the link
criteria
string to something like this:
stLinkCriteria = "(lastname = """ & Me.lastname & _
""") AND (firstname = """ & Me.firstname & """)"

Okay, I think I figured out the GROUP BY clause. I changed
everything
in
the query to Expression and took out the criteria where I had it
asking
the
the person to enter a Date of Birth. Now the Form has Two Combo
boxes.
Now
my question is how do I link the two combo boxes together so as to
when
I
click on the 2nd form It will pull the matching data. Right now It
links
the
Student Name to the 2nd form regardless of what I put in for the
birthdate.
When I use the wizard for the command button to goto the 2nd form It
only
allows me to link one field not two. How can I link both fields?

 

[Guest]

Sorry to keep bothering you, but I can not let this go. I have the format
propert set to short date in the property section of the combo and I also
checked the table to set how it was set up. It is set as date/time. I still
get the error msg." type mismatch".


Here is the code that is used in the click event of the command button.

stLinkCriteria = "[StudentName]=" & "'" & Me![Combo8] & "'" And "[DOB]=" &
"#" & Me![Combo2] & "#"


Thanks,

Robin

The issue is not that the combo is a number, but that the types are not the
same.

You say Combo2 is numeric? But you are entering a date there? If it is
unbound, set its Format property to Short Date, so Access understands it is
a date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sorry to trouble you, Let see if I can get it right this time.

(Combo2) is numeric ; Row/source - DOB; bound 1; col width 1; This
field
is were I enter a birthdate (01/01/2002)

(Combo8) is text; Row/Source - StudentName; Bound 1; Column Width 1.5;
This
field is were I enter the student name such as "Doe, John".

You indicated that a bound field that is a numeric will have a type
mismatch. The Combo2 is numeric. I need to type the student name and
then
enter the birthdate so that the next form will pull the right record. How
can I fix the combo field?

Thanks,

Robin

The "Type mismatch" message indicates Access thinks you are comparing 2
different kinds of things, such as comparing a number to a name.

Look at the properties of Combo8. The RowSource shows which columns it
has,
and the bound column tells you which one is the value of the combo. The
Column Widths property might make the bound column zero-width, in which
case
you do not see the real value. If the Bound Column refers to a Number
type
field, you have a type mismatch.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to thank you for your help. I was trying to use the following
statement in the property section in the click event of the command
button
on
my form, but I am receiving this error. "Type mismatch"

Could use take a look at the code that I put in to see where I went
wrong.
We were trying to link two combo boxes together so that that record
would
open on the 2nd form.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Student Registration 04232006"


stLinkCriteria = "[StudentName]=" & "'" & Me![Combo8] & "'" And
"[DOB]="
& "#" & Me![Combo2] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command11_Click:
Exit Sub

Err_Command11_Click:
MsgBox Err.Description
Resume Exit_Command11_Click

End Sub


Thanks,

Robin


:

Link on the primary key field, and you cannot go wrong.

If you want to link on the 2 fields, you could change the link
criteria
string to something like this:
stLinkCriteria = "(lastname = """ & Me.lastname & _
""") AND (firstname = """ & Me.firstname & """)"

Okay, I think I figured out the GROUP BY clause. I changed
everything
in
the query to Expression and took out the criteria where I had it
asking
the
the person to enter a Date of Birth. Now the Form has Two Combo
boxes.
Now
my question is how do I link the two combo boxes together so as to
when
I
click on the 2nd form It will pull the matching data. Right now It
links
the
Student Name to the 2nd form regardless of what I put in for the
birthdate.
When I use the wizard for the command button to goto the 2nd form It
only
allows me to link one field not two. How can I link both fields?

 

[Allen Browne]

The string delimiters are wrong.

Try:
stLinkCriteria = "([StudentName] = """ & Me![Combo8] & _
""") AND ([DOB] = #" & Me![Combo2] & "#)"
Debug.Print stLinkCriteria

If it still fails, open the Immediate Window (Ctrl+G) and look what the
Debug.Print statement shows. Does the string look correct as the WHERE
clause of a query?

If you still can't see it, try half at a time:
stLinkCriteria = "([StudentName]=""" & Me![Combo8] & """)"

If that works, try the other half:
stLinkCriteria = "([DOB] = #" & Me![Combo2] & "#)"

 

[Guest]

Finally I got it. I really want to thank you for helping me. So now that I
have the two combos matching up to open the 2nd form, I need to due the
verification routine. I have a field called "verification" it is a text
field. I also marked it as required in the table. On the 2nd form the user
needs to mark this field with either Yes or No it can not be left blank. I
did as you said and put the following in the 2nd forms properties under the
event Before Update:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me!Verification.Value Then

Cancel = True
MsgBox "Must fill in Verification before the record can be saved "

End If


End Sub


When I click on the command Button which is a close form on the 2nd form it
goes to the first form which is correct only if the "verification" field is
not blank. The field is left blank it needs to send the user a msg telling
them that they need to enter either Yes or No in the verification field.

I purposely left the field blank to see what it would do and it went to the
first form to start over. It should have given me a error msg.

Thanks again for your help,

Robin

The string delimiters are wrong.

Try:
stLinkCriteria = "([StudentName] = """ & Me![Combo8] & _
""") AND ([DOB] = #" & Me![Combo2] & "#)"
Debug.Print stLinkCriteria

If it still fails, open the Immediate Window (Ctrl+G) and look what the
Debug.Print statement shows. Does the string look correct as the WHERE
clause of a query?

If you still can't see it, try half at a time:
stLinkCriteria = "([StudentName]=""" & Me![Combo8] & """)"

If that works, try the other half:
stLinkCriteria = "([DOB] = #" & Me![Combo2] & "#)"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sorry to keep bothering you, but I can not let this go. I have the
format
propert set to short date in the property section of the combo and I also
checked the table to set how it was set up. It is set as date/time. I
still
get the error msg." type mismatch".


Here is the code that is used in the click event of the command button.

stLinkCriteria = "[StudentName]=" & "'" & Me![Combo8] & "'" And "[DOB]="
&
"#" & Me![Combo2] & "#"


Thanks,

Robin

 

[Allen Browne]

This is a bug in Access. If the record cannot be saved with you use the
Close action (macro) or method (VBA code), it silently discards the entry!
If fails to give you the error message you expect

More info:
http://allenbrowne.com/bug-01.html

As the article explains, you can avert the problem by explicitly saving the
record before you use Close, e.g.:
If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, Me.name

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Finally I got it. I really want to thank you for helping me. So now that
I
have the two combos matching up to open the 2nd form, I need to due the
verification routine. I have a field called "verification" it is a text
field. I also marked it as required in the table. On the 2nd form the user
needs to mark this field with either Yes or No it can not be left blank.
I
did as you said and put the following in the 2nd forms properties under
the
event Before Update:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me!Verification.Value Then

Cancel = True
MsgBox "Must fill in Verification before the record can be saved "

End If


End Sub


When I click on the command Button which is a close form on the 2nd form
it
goes to the first form which is correct only if the "verification" field
is
not blank. The field is left blank it needs to send the user a msg
telling
them that they need to enter either Yes or No in the verification field.

I purposely left the field blank to see what it would do and it went to
the
first form to start over. It should have given me a error msg.

Thanks again for your help,

Robin

The string delimiters are wrong.

Try:
stLinkCriteria = "([StudentName] = """ & Me![Combo8] & _
""") AND ([DOB] = #" & Me![Combo2] & "#)"
Debug.Print stLinkCriteria

If it still fails, open the Immediate Window (Ctrl+G) and look what the
Debug.Print statement shows. Does the string look correct as the WHERE
clause of a query?

If you still can't see it, try half at a time:
stLinkCriteria = "([StudentName]=""" & Me![Combo8] & """)"

If that works, try the other half:
stLinkCriteria = "([DOB] = #" & Me![Combo2] & "#)"

Sorry to keep bothering you, but I can not let this go. I have the
format
propert set to short date in the property section of the combo and I
also
checked the table to set how it was set up. It is set as date/time. I
still
get the error msg." type mismatch".


Here is the code that is used in the click event of the command button.

stLinkCriteria = "[StudentName]=" & "'" & Me![Combo8] & "'" And
"[DOB]="
&
"#" & Me![Combo2] & "#"

 

[Guest]

Is there a way NOT to close the form when a required field is left blank. I
want to force the user to put something in this field if left blank. The
Data that they need to put in will be Yes or No.

Thanks,

Robin

This is a bug in Access. If the record cannot be saved with you use the
Close action (macro) or method (VBA code), it silently discards the entry!
If fails to give you the error message you expect

More info:
http://allenbrowne.com/bug-01.html

As the article explains, you can avert the problem by explicitly saving the
record before you use Close, e.g.:
If Me.Dirty Then
Me.Dirty = False
End If
DoCmd.Close acForm, Me.name

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Finally I got it. I really want to thank you for helping me. So now that
I
have the two combos matching up to open the 2nd form, I need to due the
verification routine. I have a field called "verification" it is a text
field. I also marked it as required in the table. On the 2nd form the user
needs to mark this field with either Yes or No it can not be left blank.
I
did as you said and put the following in the 2nd forms properties under
the
event Before Update:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not Me!Verification.Value Then

Cancel = True
MsgBox "Must fill in Verification before the record can be saved "

End If


End Sub


When I click on the command Button which is a close form on the 2nd form
it
goes to the first form which is correct only if the "verification" field
is
not blank. The field is left blank it needs to send the user a msg
telling
them that they need to enter either Yes or No in the verification field.

I purposely left the field blank to see what it would do and it went to
the
first form to start over. It should have given me a error msg.

Thanks again for your help,

Robin

The string delimiters are wrong.

Try:
stLinkCriteria = "([StudentName] = """ & Me![Combo8] & _
""") AND ([DOB] = #" & Me![Combo2] & "#)"
Debug.Print stLinkCriteria

If it still fails, open the Immediate Window (Ctrl+G) and look what the
Debug.Print statement shows. Does the string look correct as the WHERE
clause of a query?

If you still can't see it, try half at a time:
stLinkCriteria = "([StudentName]=""" & Me![Combo8] & """)"

If that works, try the other half:
stLinkCriteria = "([DOB] = #" & Me![Combo2] & "#)"

Sorry to keep bothering you, but I can not let this go. I have the
format
propert set to short date in the property section of the combo and I
also
checked the table to set how it was set up. It is set as date/time. I
still
get the error msg." type mismatch".


Here is the code that is used in the click event of the command button.

stLinkCriteria = "[StudentName]=" & "'" & Me![Combo8] & "'" And
"[DOB]="
&
"#" & Me![Combo2] & "#"

 

[Allen Browne]

If you add the code to force the save, the save will fail.
It will generate a trappable error.
As a result the close will not occur.
I think that's exactly what you want?

 

[Guest]

Where do I put the following code to make this work?

If Me.Dirty Then
Me.Dirty = False
End If

thnaks,

Robin