Set record from subform to current record in Mainform

Crystal,

OK, I set the 'doubleclick' option event and insert the code in the design
view of the subForm.

I have a question on the Private Function code insert. When I save this from
the code Window after Debugging, am I supposed to save this as a Module or
just hit the (disk icon) and let it save it however it normally does?

It also asks me to update the main form and subform which I did. (I do have
a backup of file, so each time it doesn't work I revert back to the origonal).

The result when double-clicking the "editing" subform in normal view results
in an error (The Expression you entered has an invalid reference to the
Parent Property).

But when I doubleclick the partnumber field in the subform on the mainform,
nothing is happening. No error message there, so I'm thinking when I get the
error it's probabaly because the main form is not open.

I'll keep trying...

I will also e-mail you for your VBA. That's quite an acheivement.

Thank You


"strive4peace" wrote:

Hi Kelly,

no, that was to show that an assumption was made that PartNum contains
text since quote delimiters were used If PartNum is actually
numeric, delimiters would not be used.

I send out the first 3 chapters of a book I am writing on VBA to all who
request it -- email me and ask smile -- put "VBA" in the subject line
so I notice your message


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Good Morning Crystal,

I've got a couple of simple questions on the code that I need to insert.

At the end of the code there is the following after the --- End Function

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

Am I supposed to put the following in the code also?
Where
PartNum is a string

I'm thinking 'No' but maybe I do?

Thank You!!
Kelly

"strive4peace" wrote:

you're welcome, Kelly happy to help

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Awsome! You are so Good!!!

I was trying on the main form, but that didn't quite work. I'm very poitive
that this will give me the result I'm looking for.

I'll let you know the results.

Thank you,

Kelly Rosal

"strive4peace" wrote:

Hello (what is your name),

you're welcome

"behind the main form? Would this be in a Module possibly?"

yes, from the design view of the (sub)form --
View, Code

then, paste the code, change references if necessary, and debug (from
menu -- Debug, Compile)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal,
I really appreciate the time you've given me. I do have an auto ID assigned
to the records in the main form data entry and also it's in the query which
creates the subform.

I've got a lot to learn on the exspression and sql stuff and I have a
question on the (me.) I am supposed to leave it in there like that or is me.
refering to something that I'm supposed to subsitute.

I will put your information =FindMainPartNum() in the double-click event and
when

Where would I put the other items behind the main form? Would this be in a
Module possibly?

Thank You so much Crystal, I'm very greatful...



"strive4peace" wrote:

find record in main form
---

Hi Kelly,

this goes on the double-click event of the subform part number --
=FindMainPartNum()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
End If

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

~~~

btw, you really should use an autonumber field like PartID, to identify
your records

Long Integers (which an Autonumber is) take 4 byes to store whereas
string take about a byte per character

-- make sure also that you have at changed the Size of PartNum in your
table designs to not be 50 (which is the default)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal,
The main form, which is based off of a table that has (3) fields

PartNum, StartTime, & a set of Check boxes. The Check boxes indicate Job
Complete or Incomplete.

The subform, which is in datasheet view, on this Main Form has the PartNum,
StartTime, and of any jobs resulting from the check box above, that are not
checked as complete and are"Incomplete". I have the Incomplete= to 1 and the
Completed = to 2

I need to be able to double click one of the items in the subform
"Incomplete" and have it appear in this main form as the current Record so
the user can check this job to be "Complete".

I hope this answers your question. If you need anymore information, please
let me know.

Thank you for the reply!

Kelly



"strive4peace" wrote:

Hi Kelly,

"I need to be able to double click an incomplete record in the part
number feild and have this record become the current record."

Do you mean you want to change the focus to another form or a subform?
If so, please specify and we can help you


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
I have a form that has subform containing 3 fields. One field is a The
subform is in datasheet view showing jobs that are incomplete which may
consist of a few rows.

I need to be able to double click an incomplete record in the part number
feild and have this record become the current record.

I've been looking all over for an answer, but not quite sure if it's quite
the same. I'm not that new to Access, but the sql and expressions I am.

I've been trying to figure this one out for a few weeks and hopefully
someone can help me.

Thank you!!
Kelly

code: TestIfSubform, How to Create a General Module, Access Basics
---


Hi Kelly,

"insert the code in the design view of the subform"

the code needs to be put in the module behind the form. To see this
module, from the menu in design view, choose --
View, Code

after inserting the code and debugging, it will be saved automatically
when you save the form. You can click the diskette icon in the code
view or the form view to save the form

"invalid reference to the Parent Property"

the code will only work when the form is a subform on the parent form.
You can incorporate a test to see if the subform is opened by itself or
as a subform and only do the find if it is a subform:

put this into a general module

'~~~~~~~~~~~~~~~~~
Private Function TestIfSubform(pForm as form)
Dim mStr As String
On Error Resume Next
mStr = pForm.Parent.Name
If Err.Number 0 Then
TestIfSubform = false
Else
TestIfSubform = true
End If
end function
'~~~~~~~~~~~~~~~~~

and incorporate this function into your code...

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
end if

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
else
msgbox "Part Number " & me.PartNum & " was not found" _
,, "Error locating part"
End If

End Function
'~~~~~~~~~~~~~~~`

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

Make sure to give the module a good name when you save it. You can have
several procedures in a module, so I like to group them.

In addition to the VBA chapters I sent you, here is another good
reference to prepare yourself for programming with Access:

Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access -- good foundation for learning programming


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

OK, I set the 'doubleclick' option event and insert the code in the design
view of the subForm.

I have a question on the Private Function code insert. When I save this from
the code Window after Debugging, am I supposed to save this as a Module or
just hit the (disk icon) and let it save it however it normally does?

It also asks me to update the main form and subform which I did. (I do have
a backup of file, so each time it doesn't work I revert back to the origonal).

The result when double-clicking the "editing" subform in normal view results
in an error (The Expression you entered has an invalid reference to the
Parent Property).

But when I doubleclick the partnumber field in the subform on the mainform,
nothing is happening. No error message there, so I'm thinking when I get the
error it's probabaly because the main form is not open.

I'll keep trying...

I will also e-mail you for your VBA. That's quite an acheivement.

Thank You


"strive4peace" wrote:

Hi Kelly,

no, that was to show that an assumption was made that PartNum contains
text since quote delimiters were used If PartNum is actually
numeric, delimiters would not be used.

I send out the first 3 chapters of a book I am writing on VBA to all who
request it -- email me and ask smile -- put "VBA" in the subject line
so I notice your message


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Good Morning Crystal,

I've got a couple of simple questions on the code that I need to insert.

At the end of the code there is the following after the --- End Function

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

Am I supposed to put the following in the code also?
Where
PartNum is a string

I'm thinking 'No' but maybe I do?

Thank You!!
Kelly

"strive4peace" wrote:

you're welcome, Kelly happy to help

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Awsome! You are so Good!!!

I was trying on the main form, but that didn't quite work. I'm very poitive
that this will give me the result I'm looking for.

I'll let you know the results.

Thank you,

Kelly Rosal

"strive4peace" wrote:

Hello (what is your name),

you're welcome

"behind the main form? Would this be in a Module possibly?"

yes, from the design view of the (sub)form --
View, Code

then, paste the code, change references if necessary, and debug (from
menu -- Debug, Compile)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal,
I really appreciate the time you've given me. I do have an auto ID assigned
to the records in the main form data entry and also it's in the query which
creates the subform.

I've got a lot to learn on the exspression and sql stuff and I have a
question on the (me.) I am supposed to leave it in there like that or is me.
refering to something that I'm supposed to subsitute.

I will put your information =FindMainPartNum() in the double-click event and
when

Where would I put the other items behind the main form? Would this be in a
Module possibly?

Thank You so much Crystal, I'm very greatful...



"strive4peace" wrote:

find record in main form
---

Hi Kelly,

this goes on the double-click event of the subform part number --
=FindMainPartNum()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
End If

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

~~~

btw, you really should use an autonumber field like PartID, to identify
your records

Long Integers (which an Autonumber is) take 4 byes to store whereas
string take about a byte per character

-- make sure also that you have at changed the Size of PartNum in your
table designs to not be 50 (which is the default)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal,
The main form, which is based off of a table that has (3) fields

PartNum, StartTime, & a set of Check boxes. The Check boxes indicate Job
Complete or Incomplete.

The subform, which is in datasheet view, on this Main Form has the PartNum,
StartTime, and of any jobs resulting from the check box above, that are not
checked as complete and are"Incomplete". I have the Incomplete= to 1 and the
Completed = to 2

I need to be able to double click one of the items in the subform
"Incomplete" and have it appear in this main form as the current Record so
the user can check this job to be "Complete".

I hope this answers your question. If you need anymore information, please
let me know.

Thank you for the reply!

Kelly



"strive4peace" wrote:

Hi Kelly,

"I need to be able to double click an incomplete record in the part
number feild and have this record become the current record."

Do you mean you want to change the focus to another form or a subform?
If so, please specify and we can help you


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
I have a form that has subform containing 3 fields. One field is a The
subform is in datasheet view showing jobs that are incomplete which may
consist of a few rows.

I need to be able to double click an incomplete record in the part number
feild and have this record become the current record.

I've been looking all over for an answer, but not quite sure if it's quite
the same. I'm not that new to Access, but the sql and expressions I am.

I've been trying to figure this one out for a few weeks and hopefully
someone can help me.

Thank you!!
Kelly

Hi Crystal. Thank You!

I will follow the procedures that you've given me and let you know when I've
got it working. I wasn't sure or to familiar with the designing of a module
nor any other code. You're a genious in my eyes!

I can't thank you enough!!

Sincerely,
Kelly

"strive4peace" wrote:

code: TestIfSubform, How to Create a General Module, Access Basics
---


Hi Kelly,

"insert the code in the design view of the subform"

the code needs to be put in the module behind the form. To see this
module, from the menu in design view, choose --
View, Code

after inserting the code and debugging, it will be saved automatically
when you save the form. You can click the diskette icon in the code
view or the form view to save the form

"invalid reference to the Parent Property"

the code will only work when the form is a subform on the parent form.
You can incorporate a test to see if the subform is opened by itself or
as a subform and only do the find if it is a subform:

put this into a general module

'~~~~~~~~~~~~~~~~~
Private Function TestIfSubform(pForm as form)
Dim mStr As String
On Error Resume Next
mStr = pForm.Parent.Name
If Err.Number 0 Then
TestIfSubform = false
Else
TestIfSubform = true
End If
end function
'~~~~~~~~~~~~~~~~~

and incorporate this function into your code...

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
end if

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
else
msgbox "Part Number " & me.PartNum & " was not found" _
,, "Error locating part"
End If

End Function
'~~~~~~~~~~~~~~~`

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

Make sure to give the module a good name when you save it. You can have
several procedures in a module, so I like to group them.

In addition to the VBA chapters I sent you, here is another good
reference to prepare yourself for programming with Access:

Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access -- good foundation for learning programming


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

OK, I set the 'doubleclick' option event and insert the code in the design
view of the subForm.

I have a question on the Private Function code insert. When I save this from
the code Window after Debugging, am I supposed to save this as a Module or
just hit the (disk icon) and let it save it however it normally does?

It also asks me to update the main form and subform which I did. (I do have
a backup of file, so each time it doesn't work I revert back to the origonal).

The result when double-clicking the "editing" subform in normal view results
in an error (The Expression you entered has an invalid reference to the
Parent Property).

But when I doubleclick the partnumber field in the subform on the mainform,
nothing is happening. No error message there, so I'm thinking when I get the
error it's probabaly because the main form is not open.

I'll keep trying...

I will also e-mail you for your VBA. That's quite an acheivement.

Thank You


"strive4peace" wrote:

Hi Kelly,

no, that was to show that an assumption was made that PartNum contains
text since quote delimiters were used If PartNum is actually
numeric, delimiters would not be used.

I send out the first 3 chapters of a book I am writing on VBA to all who
request it -- email me and ask smile -- put "VBA" in the subject line
so I notice your message


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Good Morning Crystal,

I've got a couple of simple questions on the code that I need to insert.

At the end of the code there is the following after the --- End Function

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

Am I supposed to put the following in the code also?
Where
PartNum is a string

I'm thinking 'No' but maybe I do?

Thank You!!
Kelly

"strive4peace" wrote:

you're welcome, Kelly happy to help

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Awsome! You are so Good!!!

I was trying on the main form, but that didn't quite work. I'm very poitive
that this will give me the result I'm looking for.

I'll let you know the results.

Thank you,

Kelly Rosal

"strive4peace" wrote:

Hello (what is your name),

you're welcome

"behind the main form? Would this be in a Module possibly?"

yes, from the design view of the (sub)form --
View, Code

then, paste the code, change references if necessary, and debug (from
menu -- Debug, Compile)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal,
I really appreciate the time you've given me. I do have an auto ID assigned
to the records in the main form data entry and also it's in the query which
creates the subform.

I've got a lot to learn on the exspression and sql stuff and I have a
question on the (me.) I am supposed to leave it in there like that or is me.
refering to something that I'm supposed to subsitute.

I will put your information =FindMainPartNum() in the double-click event and
when

Where would I put the other items behind the main form? Would this be in a
Module possibly?

Thank You so much Crystal, I'm very greatful...



"strive4peace" wrote:

find record in main form
---

Hi Kelly,

this goes on the double-click event of the subform part number --
=FindMainPartNum()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it

you're welcome, Kelly

a genius? gee, thanks blush

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal. Thank You!

I will follow the procedures that you've given me and let you know when I've
got it working. I wasn't sure or to familiar with the designing of a module
nor any other code. You're a genious in my eyes!

I can't thank you enough!!

Sincerely,
Kelly

"strive4peace" wrote:

code: TestIfSubform, How to Create a General Module, Access Basics
---


Hi Kelly,

"insert the code in the design view of the subform"

the code needs to be put in the module behind the form. To see this
module, from the menu in design view, choose --
View, Code

after inserting the code and debugging, it will be saved automatically
when you save the form. You can click the diskette icon in the code
view or the form view to save the form

"invalid reference to the Parent Property"

the code will only work when the form is a subform on the parent form.
You can incorporate a test to see if the subform is opened by itself or
as a subform and only do the find if it is a subform:

put this into a general module

'~~~~~~~~~~~~~~~~~
Private Function TestIfSubform(pForm as form)
Dim mStr As String
On Error Resume Next
mStr = pForm.Parent.Name
If Err.Number 0 Then
TestIfSubform = false
Else
TestIfSubform = true
End If
end function
'~~~~~~~~~~~~~~~~~

and incorporate this function into your code...

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
end if

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
else
msgbox "Part Number " & me.PartNum & " was not found" _
,, "Error locating part"
End If

End Function
'~~~~~~~~~~~~~~~`

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

Make sure to give the module a good name when you save it. You can have
several procedures in a module, so I like to group them.

In addition to the VBA chapters I sent you, here is another good
reference to prepare yourself for programming with Access:

Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access -- good foundation for learning programming


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

OK, I set the 'doubleclick' option event and insert the code in the design
view of the subForm.

I have a question on the Private Function code insert. When I save this from
the code Window after Debugging, am I supposed to save this as a Module or
just hit the (disk icon) and let it save it however it normally does?

It also asks me to update the main form and subform which I did. (I do have
a backup of file, so each time it doesn't work I revert back to the origonal).

The result when double-clicking the "editing" subform in normal view results
in an error (The Expression you entered has an invalid reference to the
Parent Property).

But when I doubleclick the partnumber field in the subform on the mainform,
nothing is happening. No error message there, so I'm thinking when I get the
error it's probabaly because the main form is not open.

I'll keep trying...

I will also e-mail you for your VBA. That's quite an acheivement.

Thank You


"strive4peace" wrote:

Hi Kelly,

no, that was to show that an assumption was made that PartNum contains
text since quote delimiters were used If PartNum is actually
numeric, delimiters would not be used.

I send out the first 3 chapters of a book I am writing on VBA to all who
request it -- email me and ask smile -- put "VBA" in the subject line
so I notice your message


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Good Morning Crystal,

I've got a couple of simple questions on the code that I need to insert.

At the end of the code there is the following after the --- End Function

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

Am I supposed to put the following in the code also?
Where
PartNum is a string

I'm thinking 'No' but maybe I do?

Thank You!!
Kelly

"strive4peace" wrote:

you're welcome, Kelly happy to help

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Awsome! You are so Good!!!

I was trying on the main form, but that didn't quite work. I'm very poitive
that this will give me the result I'm looking for.

I'll let you know the results.

Thank you,

Kelly Rosal

"strive4peace" wrote:

Hello (what is your name),

you're welcome

"behind the main form? Would this be in a Module possibly?"

yes, from the design view of the (sub)form --
View, Code

then, paste the code, change references if necessary, and debug (from
menu -- Debug, Compile)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal,
I really appreciate the time you've given me. I do have an auto ID assigned
to the records in the main form data entry and also it's in the query which
creates the subform.

I've got a lot to learn on the exspression and sql stuff and I have a
question on the (me.) I am supposed to leave it in there like that or is me.
refering to something that I'm supposed to subsitute.

I will put your information =FindMainPartNum() in the double-click event and
when

Where would I put the other items behind the main form? Would this be in a
Module possibly?

Thank You so much Crystal, I'm very greatful...



"strive4peace" wrote:

find record in main form
---

Hi Kelly,

this goes on the double-click event of the subform part number --
=FindMainPartNum()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it

Whoever can just roll this off the top of their head - like you can, I my
books is at least that...

Kelly

"strive4peace" wrote:

you're welcome, Kelly

a genius? gee, thanks blush

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal. Thank You!

I will follow the procedures that you've given me and let you know when I've
got it working. I wasn't sure or to familiar with the designing of a module
nor any other code. You're a genious in my eyes!

I can't thank you enough!!

Sincerely,
Kelly

"strive4peace" wrote:

code: TestIfSubform, How to Create a General Module, Access Basics
---


Hi Kelly,

"insert the code in the design view of the subform"

the code needs to be put in the module behind the form. To see this
module, from the menu in design view, choose --
View, Code

after inserting the code and debugging, it will be saved automatically
when you save the form. You can click the diskette icon in the code
view or the form view to save the form

"invalid reference to the Parent Property"

the code will only work when the form is a subform on the parent form.
You can incorporate a test to see if the subform is opened by itself or
as a subform and only do the find if it is a subform:

put this into a general module

'~~~~~~~~~~~~~~~~~
Private Function TestIfSubform(pForm as form)
Dim mStr As String
On Error Resume Next
mStr = pForm.Parent.Name
If Err.Number 0 Then
TestIfSubform = false
Else
TestIfSubform = true
End If
end function
'~~~~~~~~~~~~~~~~~

and incorporate this function into your code...

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
end if

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
else
msgbox "Part Number " & me.PartNum & " was not found" _
,, "Error locating part"
End If

End Function
'~~~~~~~~~~~~~~~`

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

Make sure to give the module a good name when you save it. You can have
several procedures in a module, so I like to group them.

In addition to the VBA chapters I sent you, here is another good
reference to prepare yourself for programming with Access:

Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access -- good foundation for learning programming


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

OK, I set the 'doubleclick' option event and insert the code in the design
view of the subForm.

I have a question on the Private Function code insert. When I save this from
the code Window after Debugging, am I supposed to save this as a Module or
just hit the (disk icon) and let it save it however it normally does?

It also asks me to update the main form and subform which I did. (I do have
a backup of file, so each time it doesn't work I revert back to the origonal).

The result when double-clicking the "editing" subform in normal view results
in an error (The Expression you entered has an invalid reference to the
Parent Property).

But when I doubleclick the partnumber field in the subform on the mainform,
nothing is happening. No error message there, so I'm thinking when I get the
error it's probabaly because the main form is not open.

I'll keep trying...

I will also e-mail you for your VBA. That's quite an acheivement.

Thank You


"strive4peace" wrote:

Hi Kelly,

no, that was to show that an assumption was made that PartNum contains
text since quote delimiters were used If PartNum is actually
numeric, delimiters would not be used.

I send out the first 3 chapters of a book I am writing on VBA to all who
request it -- email me and ask smile -- put "VBA" in the subject line
so I notice your message


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Good Morning Crystal,

I've got a couple of simple questions on the code that I need to insert.

At the end of the code there is the following after the --- End Function

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

Am I supposed to put the following in the code also?
Where
PartNum is a string

I'm thinking 'No' but maybe I do?

Thank You!!
Kelly

"strive4peace" wrote:

you're welcome, Kelly happy to help

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Awsome! You are so Good!!!

I was trying on the main form, but that didn't quite work. I'm very poitive
that this will give me the result I'm looking for.

I'll let you know the results.

Thank you,

Kelly Rosal

"strive4peace" wrote:

Hello (what is your name),

you're welcome

"behind the main form? Would this be in a Module possibly?"

yes, from the design view of the (sub)form --
View, Code

then, paste the code, change references if necessary, and debug (from
menu -- Debug, Compile)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal,
I really appreciate the time you've given me. I do have an auto ID assigned
to the records in the main form data entry and also it's in the query which
creates the subform.

I've got a lot to learn on the exspression and sql stuff and I have a
question on the (me.) I am supposed to leave it in there like that or is me.
refering to something that I'm supposed to subsitute.

I will put your information =FindMainPartNum() in the double-click event and
when

Where would I put the other items behind the main form? Would this be in a
Module possibly?

Thank You so much Crystal, I'm very greatful...



"strive4peace" wrote:

find record in main form
---

Hi Kelly,

Crystal,

Before I can give you the results of the Test, I will need to describe the
procedure I used first.

I first selected Insert/Module and pasted the module information in. I then
I ran the Debug and save the module. I next selected to creat a procedure? IS
this correct or should I just get back to the subform and view the code and
past information into current page?


Ok, Test results are;
It fails right where you have listed that it's only works on a subform.

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
Does the subform need to be "Bound" or how can it not be a subform?

Thank You!
Kelly

"strive4peace" wrote:

you're welcome, Kelly

a genius? gee, thanks blush

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal. Thank You!

I will follow the procedures that you've given me and let you know when I've
got it working. I wasn't sure or to familiar with the designing of a module
nor any other code. You're a genious in my eyes!

I can't thank you enough!!

Sincerely,
Kelly

"strive4peace" wrote:

code: TestIfSubform, How to Create a General Module, Access Basics
---


Hi Kelly,

"insert the code in the design view of the subform"

the code needs to be put in the module behind the form. To see this
module, from the menu in design view, choose --
View, Code

after inserting the code and debugging, it will be saved automatically
when you save the form. You can click the diskette icon in the code
view or the form view to save the form

"invalid reference to the Parent Property"

the code will only work when the form is a subform on the parent form.
You can incorporate a test to see if the subform is opened by itself or
as a subform and only do the find if it is a subform:

put this into a general module

'~~~~~~~~~~~~~~~~~
Private Function TestIfSubform(pForm as form)
Dim mStr As String
On Error Resume Next
mStr = pForm.Parent.Name
If Err.Number 0 Then
TestIfSubform = false
Else
TestIfSubform = true
End If
end function
'~~~~~~~~~~~~~~~~~

and incorporate this function into your code...

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
end if

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
else
msgbox "Part Number " & me.PartNum & " was not found" _
,, "Error locating part"
End If

End Function
'~~~~~~~~~~~~~~~`

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

Make sure to give the module a good name when you save it. You can have
several procedures in a module, so I like to group them.

In addition to the VBA chapters I sent you, here is another good
reference to prepare yourself for programming with Access:

Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access -- good foundation for learning programming


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

OK, I set the 'doubleclick' option event and insert the code in the design
view of the subForm.

I have a question on the Private Function code insert. When I save this from
the code Window after Debugging, am I supposed to save this as a Module or
just hit the (disk icon) and let it save it however it normally does?

It also asks me to update the main form and subform which I did. (I do have
a backup of file, so each time it doesn't work I revert back to the origonal).

The result when double-clicking the "editing" subform in normal view results
in an error (The Expression you entered has an invalid reference to the
Parent Property).

But when I doubleclick the partnumber field in the subform on the mainform,
nothing is happening. No error message there, so I'm thinking when I get the
error it's probabaly because the main form is not open.

I'll keep trying...

I will also e-mail you for your VBA. That's quite an acheivement.

Thank You


"strive4peace" wrote:

Hi Kelly,

no, that was to show that an assumption was made that PartNum contains
text since quote delimiters were used If PartNum is actually
numeric, delimiters would not be used.

I send out the first 3 chapters of a book I am writing on VBA to all who
request it -- email me and ask smile -- put "VBA" in the subject line
so I notice your message


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Good Morning Crystal,

I've got a couple of simple questions on the code that I need to insert.

At the end of the code there is the following after the --- End Function

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

Am I supposed to put the following in the code also?
Where
PartNum is a string

I'm thinking 'No' but maybe I do?

Thank You!!
Kelly

"strive4peace" wrote:

you're welcome, Kelly happy to help

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Awsome! You are so Good!!!

I was trying on the main form, but that didn't quite work. I'm very poitive
that this will give me the result I'm looking for.

I'll let you know the results.

Thank you,

Kelly Rosal

"strive4peace" wrote:

Hello (what is your name),

you're welcome

"behind the main form? Would this be in a Module possibly?"

yes, from the design view of the (sub)form --
View, Code

then, paste the code, change references if necessary, and debug (from
menu -- Debug, Compile)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal,
I really appreciate the time you've given me. I do have an auto ID assigned
to the records in the main form data entry and also it's in the query which
creates the subform.

I've got a lot to learn on the exspression and sql stuff and I have a
question on the (me.) I am supposed to leave it in there like that or is me.
refering to something that I'm supposed to subsitute.

I will put your information =FindMainPartNum() in the double-click event and
when

Where would I put the other items behind the main form? Would this be in a
Module possibly?

Thank You so much Crystal, I'm very greatful...



"strive4peace" wrote:

find record in main form
---

Hi Kelly,

Hi Kelly

you have to open the main form that the subform is on, not the subform
directly

"I next selected to creat a procedure? IS this correct or should I just
get back to the subform and view the code and past information into
current page?"

for the FindMainPartNum procedure -- follow the instructions I gave you
for getting to the code behind the form

the TestIfSubform code does go into a general module (Insert/Module
should get this too)

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

Before I can give you the results of the Test, I will need to describe the
procedure I used first.

I first selected Insert/Module and pasted the module information in. I then
I ran the Debug and save the module. I next selected to creat a procedure? IS
this correct or should I just get back to the subform and view the code and
past information into current page?


Ok, Test results are;
It fails right where you have listed that it's only works on a subform.

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
Does the subform need to be "Bound" or how can it not be a subform?

Thank You!
Kelly

"strive4peace" wrote:

you're welcome, Kelly

a genius? gee, thanks blush

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal. Thank You!

I will follow the procedures that you've given me and let you know when I've
got it working. I wasn't sure or to familiar with the designing of a module
nor any other code. You're a genious in my eyes!

I can't thank you enough!!

Sincerely,
Kelly

"strive4peace" wrote:

code: TestIfSubform, How to Create a General Module, Access Basics
---


Hi Kelly,

"insert the code in the design view of the subform"

the code needs to be put in the module behind the form. To see this
module, from the menu in design view, choose --
View, Code

after inserting the code and debugging, it will be saved automatically
when you save the form. You can click the diskette icon in the code
view or the form view to save the form

"invalid reference to the Parent Property"

the code will only work when the form is a subform on the parent form.
You can incorporate a test to see if the subform is opened by itself or
as a subform and only do the find if it is a subform:

put this into a general module

'~~~~~~~~~~~~~~~~~
Private Function TestIfSubform(pForm as form)
Dim mStr As String
On Error Resume Next
mStr = pForm.Parent.Name
If Err.Number 0 Then
TestIfSubform = false
Else
TestIfSubform = true
End If
end function
'~~~~~~~~~~~~~~~~~

and incorporate this function into your code...

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
end if

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
else
msgbox "Part Number " & me.PartNum & " was not found" _
,, "Error locating part"
End If

End Function
'~~~~~~~~~~~~~~~`

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

Make sure to give the module a good name when you save it. You can have
several procedures in a module, so I like to group them.

In addition to the VBA chapters I sent you, here is another good
reference to prepare yourself for programming with Access:

Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access -- good foundation for learning programming


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

OK, I set the 'doubleclick' option event and insert the code in the design
view of the subForm.

I have a question on the Private Function code insert. When I save this from
the code Window after Debugging, am I supposed to save this as a Module or
just hit the (disk icon) and let it save it however it normally does?

It also asks me to update the main form and subform which I did. (I do have
a backup of file, so each time it doesn't work I revert back to the origonal).

The result when double-clicking the "editing" subform in normal view results
in an error (The Expression you entered has an invalid reference to the
Parent Property).

But when I doubleclick the partnumber field in the subform on the mainform,
nothing is happening. No error message there, so I'm thinking when I get the
error it's probabaly because the main form is not open.

I'll keep trying...

I will also e-mail you for your VBA. That's quite an acheivement.

Thank You


"strive4peace" wrote:

Hi Kelly,

no, that was to show that an assumption was made that PartNum contains
text since quote delimiters were used If PartNum is actually
numeric, delimiters would not be used.

I send out the first 3 chapters of a book I am writing on VBA to all who
request it -- email me and ask smile -- put "VBA" in the subject line
so I notice your message


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Good Morning Crystal,

I've got a couple of simple questions on the code that I need to insert.

At the end of the code there is the following after the --- End Function

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

Am I supposed to put the following in the code also?
Where
PartNum is a string

I'm thinking 'No' but maybe I do?

Thank You!!
Kelly

"strive4peace" wrote:

you're welcome, Kelly happy to help

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Awsome! You are so Good!!!

I was trying on the main form, but that didn't quite work. I'm very poitive
that this will give me the result I'm looking for.

I'll let you know the results.

Thank you,

Kelly Rosal

"strive4peace" wrote:

Hello (what is your name),

you're welcome

"behind the main form? Would this be in a Module possibly?"

yes, from the design view of the (sub)form --
View, Code

then, paste the code, change references if necessary, and debug (from
menu -- Debug, Compile)


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal,
I really appreciate the time you've given me. I do have an auto ID assigned
to the records in the main form data entry and also it's in the query which
creates the subform.

I've got a lot to learn on the exspression and sql stuff and I have a
question on the (me.) I am supposed to leave it in there like that or is me.
refering to something that I'm supposed to subsitute.

I will put your information =FindMainPartNum() in the double-click event and
when

Where would I put the other items behind the main form? Would this be in a
Module possibly?

Thank You so much Crystal, I'm very greatful...



"strive4peace" wrote:

find record in main form
---

Hi Kelly,

Hi Crystal,

I'm not sure if you're available for me to call you today or if you would
like to call me? I'm at work from 7am to 4pm CST and my number is (920)
683-7014 direct or let me know what your number is. If you want to e-mail me
at work, the address is

I have tried everything the you've suggested and all seems good except for
the input of the PartNum data into the feild on the main form. When I select
the PartNum in the subform, I can see in the lower left corner of the screen
stating "Calculating" but it never places the PartNum in the MainForm
PartNum field.

I really need to get this going so I can move on to the next procedure in
this database.

I notice that previous things I've done or macros I've created to edit an
incomplete job had VBA references in the code. I have removed most if not all
of them because I figure there just making the database a little bigger.
Everything is working correctly yet, so I think that's OK? Please let me know
if you're available.

Thank You!
Kelly

"strive4peace" wrote:

Hi Kelly

you have to open the main form that the subform is on, not the subform
directly

"I next selected to creat a procedure? IS this correct or should I just
get back to the subform and view the code and past information into
current page?"

for the FindMainPartNum procedure -- follow the instructions I gave you
for getting to the code behind the form

the TestIfSubform code does go into a general module (Insert/Module
should get this too)

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

Before I can give you the results of the Test, I will need to describe the
procedure I used first.

I first selected Insert/Module and pasted the module information in. I then
I ran the Debug and save the module. I next selected to creat a procedure? IS
this correct or should I just get back to the subform and view the code and
past information into current page?


Ok, Test results are;
It fails right where you have listed that it's only works on a subform.

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
Does the subform need to be "Bound" or how can it not be a subform?

Thank You!
Kelly

"strive4peace" wrote:

you're welcome, Kelly

a genius? gee, thanks blush

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal. Thank You!

I will follow the procedures that you've given me and let you know when I've
got it working. I wasn't sure or to familiar with the designing of a module
nor any other code. You're a genious in my eyes!

I can't thank you enough!!

Sincerely,
Kelly

"strive4peace" wrote:

code: TestIfSubform, How to Create a General Module, Access Basics
---


Hi Kelly,

"insert the code in the design view of the subform"

the code needs to be put in the module behind the form. To see this
module, from the menu in design view, choose --
View, Code

after inserting the code and debugging, it will be saved automatically
when you save the form. You can click the diskette icon in the code
view or the form view to save the form

"invalid reference to the Parent Property"

the code will only work when the form is a subform on the parent form.
You can incorporate a test to see if the subform is opened by itself or
as a subform and only do the find if it is a subform:

put this into a general module

'~~~~~~~~~~~~~~~~~
Private Function TestIfSubform(pForm as form)
Dim mStr As String
On Error Resume Next
mStr = pForm.Parent.Name
If Err.Number 0 Then
TestIfSubform = false
Else
TestIfSubform = true
End If
end function
'~~~~~~~~~~~~~~~~~

and incorporate this function into your code...

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
end if

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
else
msgbox "Part Number " & me.PartNum & " was not found" _
,, "Error locating part"
End If

End Function
'~~~~~~~~~~~~~~~`

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

Make sure to give the module a good name when you save it. You can have
several procedures in a module, so I like to group them.

In addition to the VBA chapters I sent you, here is another good
reference to prepare yourself for programming with Access:

Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access -- good foundation for learning programming


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

OK, I set the 'doubleclick' option event and insert the code in the design
view of the subForm.

I have a question on the Private Function code insert. When I save this from
the code Window after Debugging, am I supposed to save this as a Module or
just hit the (disk icon) and let it save it however it normally does?

It also asks me to update the main form and subform which I did. (I do have
a backup of file, so each time it doesn't work I revert back to the origonal).

The result when double-clicking the "editing" subform in normal view results
in an error (The Expression you entered has an invalid reference to the
Parent Property).

But when I doubleclick the partnumber field in the subform on the mainform,
nothing is happening. No error message there, so I'm thinking when I get the
error it's probabaly because the main form is not open.

I'll keep trying...

I will also e-mail you for your VBA. That's quite an acheivement.

Thank You


"strive4peace" wrote:

Hi Kelly,

no, that was to show that an assumption was made that PartNum contains
text since quote delimiters were used If PartNum is actually
numeric, delimiters would not be used.

I send out the first 3 chapters of a book I am writing on VBA to all who
request it -- email me and ask smile -- put "VBA" in the subject line
so I notice your message


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Good Morning Crystal,

I've got a couple of simple questions on the code that I need to insert.

At the end of the code there is the following after the --- End Function

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

Am I supposed to put the following in the code also?
Where
PartNum is a string

I'm thinking 'No' but maybe I do?

Thank You!!
Kelly

"strive4peace" wrote:

you're welcome, Kelly happy to help

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Awsome! You are so Good!!!

I was trying on the main form, but that didn't quite work. I'm very poitive
that this will give me the result I'm looking for.

I'll let you know the results.

Thank you,

Kelly Rosal

"strive4peace" wrote:

Hi Kelly,

got your database

"In the Form view of frmNewPrtAmi I do notice on the lower left screen
that it runs something, but does not add the new part number to the
PartNum field."

try
me.qryAmiIncompl_subform.Requery
on the form AfterUpdate event

"Also, for some reason in the qryAmiIncompl subform I cannot get it to
be unique records only. "

inclusion of the ID field will make every record show since IDs are unique

~~~

if you want the subforms to show just the partnumber on the main form,
you need to use the LinkMasterField and LinkChildField properties of the
subform control

LinkMasterField -- PartNum
LinkChildField -- PartNum


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal,

I'm not sure if you're available for me to call you today or if you would
like to call me? I'm at work from 7am to 4pm CST and my number is (920)
683-7014 direct or let me know what your number is. If you want to e-mail me
at work, the address is

I have tried everything the you've suggested and all seems good except for
the input of the PartNum data into the feild on the main form. When I select
the PartNum in the subform, I can see in the lower left corner of the screen
stating "Calculating" but it never places the PartNum in the MainForm
PartNum field.

I really need to get this going so I can move on to the next procedure in
this database.

I notice that previous things I've done or macros I've created to edit an
incomplete job had VBA references in the code. I have removed most if not all
of them because I figure there just making the database a little bigger.
Everything is working correctly yet, so I think that's OK? Please let me know
if you're available.

Thank You!
Kelly

"strive4peace" wrote:

Hi Kelly

you have to open the main form that the subform is on, not the subform
directly

"I next selected to creat a procedure? IS this correct or should I just
get back to the subform and view the code and past information into
current page?"

for the FindMainPartNum procedure -- follow the instructions I gave you
for getting to the code behind the form

the TestIfSubform code does go into a general module (Insert/Module
should get this too)

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

Before I can give you the results of the Test, I will need to describe the
procedure I used first.

I first selected Insert/Module and pasted the module information in. I then
I ran the Debug and save the module. I next selected to creat a procedure? IS
this correct or should I just get back to the subform and view the code and
past information into current page?


Ok, Test results are;
It fails right where you have listed that it's only works on a subform.

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
Does the subform need to be "Bound" or how can it not be a subform?

Thank You!
Kelly

"strive4peace" wrote:

you're welcome, Kelly

a genius? gee, thanks blush

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Hi Crystal. Thank You!

I will follow the procedures that you've given me and let you know when I've
got it working. I wasn't sure or to familiar with the designing of a module
nor any other code. You're a genious in my eyes!

I can't thank you enough!!

Sincerely,
Kelly

"strive4peace" wrote:

code: TestIfSubform, How to Create a General Module, Access Basics
---


Hi Kelly,

"insert the code in the design view of the subform"

the code needs to be put in the module behind the form. To see this
module, from the menu in design view, choose --
View, Code

after inserting the code and debugging, it will be saved automatically
when you save the form. You can click the diskette icon in the code
view or the form view to save the form

"invalid reference to the Parent Property"

the code will only work when the form is a subform on the parent form.
You can incorporate a test to see if the subform is opened by itself or
as a subform and only do the find if it is a subform:

put this into a general module

'~~~~~~~~~~~~~~~~~
Private Function TestIfSubform(pForm as form)
Dim mStr As String
On Error Resume Next
mStr = pForm.Parent.Name
If Err.Number 0 Then
TestIfSubform = false
Else
TestIfSubform = true
End If
end function
'~~~~~~~~~~~~~~~~~

and incorporate this function into your code...

'~~~~~~~~~~~~~~~~~~~~
Private Function FindMainPartNum()

'save current record if changes were made
If me.dirty then me.dirty = false

If Not TestIfSubform(Me) then
msgbox "This only works when used as a subform",,"Note"
exit sub
end if

'if not on a current record then exit
If me.NewRecord Then
MsgBox "You are not on current record" _
,,"Cannot move to record"
Exit Function
end if

'if nothing is picked in the part number control, exit
If IsNull(Me.PartNum) Then
MsgBox "Part Number is not filled out" _
,,"Cannot move to record"
Exit Function
end if

'find the first value that matches in the main form
Me.parent.RecordsetClone.FindFirst "PartNum= '" & me.PartNum & "'"

'if a matching record was found, then move to it
If Not Me.parent.RecordsetClone.NoMatch Then
Me.parent.Bookmark = Me.parent.RecordsetClone.Bookmark
else
msgbox "Part Number " & me.PartNum & " was not found" _
,, "Error locating part"
End If

End Function
'~~~~~~~~~~~~~~~`

How to Create a General Module

1. from the database window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear to happen
-- this is good

Make sure to give the module a good name when you save it. You can have
several procedures in a module, so I like to group them.

In addition to the VBA chapters I sent you, here is another good
reference to prepare yourself for programming with Access:

Access Basics
http://allenbrowne.com/casu-22.html
This 30-page training tutorial will orient you toward the core concepts
of Microsoft Access -- good foundation for learning programming


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Crystal,

OK, I set the 'doubleclick' option event and insert the code in the design
view of the subForm.

I have a question on the Private Function code insert. When I save this from
the code Window after Debugging, am I supposed to save this as a Module or
just hit the (disk icon) and let it save it however it normally does?

It also asks me to update the main form and subform which I did. (I do have
a backup of file, so each time it doesn't work I revert back to the origonal).

The result when double-clicking the "editing" subform in normal view results
in an error (The Expression you entered has an invalid reference to the
Parent Property).

But when I doubleclick the partnumber field in the subform on the mainform,
nothing is happening. No error message there, so I'm thinking when I get the
error it's probabaly because the main form is not open.

I'll keep trying...

I will also e-mail you for your VBA. That's quite an acheivement.

Thank You


"strive4peace" wrote:

Hi Kelly,

no, that was to show that an assumption was made that PartNum contains
text since quote delimiters were used If PartNum is actually
numeric, delimiters would not be used.

I send out the first 3 chapters of a book I am writing on VBA to all who
request it -- email me and ask smile -- put "VBA" in the subject line
so I notice your message


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Good Morning Crystal,

I've got a couple of simple questions on the code that I need to insert.

At the end of the code there is the following after the --- End Function

End Function
'~~~~~~~~~~~~~~~`

WHERE
PartNum is a string

Am I supposed to put the following in the code also?
Where
PartNum is a string

I'm thinking 'No' but maybe I do?

Thank You!!
Kelly

"strive4peace" wrote:

you're welcome, Kelly happy to help

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



netadmin wrote:
Awsome! You are so Good!!!

I was trying on the main form, but that didn't quite work. I'm very poitive
that this will give me the result I'm looking for.

I'll let you know the results.

Thank you,

Kelly Rosal

"strive4peace" wrote: