"dummies guide" to opening subform with new record using a value f

[Krazy Darcy]

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Cheese_whiz]

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW

 

[Krazy Darcy]

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Cheese_whiz]

Hi Darcy,

Just put that middle line of my code in the on_click event of the button you
are using to open the second form. Do that by opening your main form in
design view, clicking on the button, opening the properties window (f4),
clicking on the 'events' tab, and finding the "on click" event. Double click
in the text area beside the "on_click", and "event procedure" should fill the
blank area. Also, just to the right of that blank area, a button with "..."
on it will appear. Click that button (after you get the "event procedure" to
appear", and your form's vba module will appear with a new procedure opening
and closing lines already added. It will look like this:

Sub Private MyButtonName_Click()

End Sub

Between those lines goes my DoCmd....... line from my previous post.
Again, make sure the field names are correct in my line by checking the field
names on each form and comparing those with the names I used in that line.

Hope that helps,
CW

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Krazy Darcy]

I have even checked the case of the letters in the file and field names. The
button opens the form art-use but doesn't seem to fill in the record number
field in the art-use form.

It's as if it is behaving like it is wanting to display record(s) that has
the record number that is on the artworksdetails form (the form the button is
on)

thanks

Hi Darcy,

Just put that middle line of my code in the on_click event of the button you
are using to open the second form. Do that by opening your main form in
design view, clicking on the button, opening the properties window (f4),
clicking on the 'events' tab, and finding the "on click" event. Double click
in the text area beside the "on_click", and "event procedure" should fill the
blank area. Also, just to the right of that blank area, a button with "..."
on it will appear. Click that button (after you get the "event procedure" to
appear", and your form's vba module will appear with a new procedure opening
and closing lines already added. It will look like this:

Sub Private MyButtonName_Click()

End Sub

Between those lines goes my DoCmd....... line from my previous post.
Again, make sure the field names are correct in my line by checking the field
names on each form and comparing those with the names I used in that line.

Hope that helps,
CW

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW



:

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Cheese_whiz]

Is your main form on an existing record when you click on the button to open
the other form?

Does the popup form control name [Record Number] happen to be the exact same
as the field it's bound to? (Read: 'name' on 'other' tab the same as
'control source' on 'data' tab)?

CW

I have even checked the case of the letters in the file and field names. The
button opens the form art-use but doesn't seem to fill in the record number
field in the art-use form.

It's as if it is behaving like it is wanting to display record(s) that has
the record number that is on the artworksdetails form (the form the button is
on)

thanks

Hi Darcy,

Just put that middle line of my code in the on_click event of the button you
are using to open the second form. Do that by opening your main form in
design view, clicking on the button, opening the properties window (f4),
clicking on the 'events' tab, and finding the "on click" event. Double click
in the text area beside the "on_click", and "event procedure" should fill the
blank area. Also, just to the right of that blank area, a button with "..."
on it will appear. Click that button (after you get the "event procedure" to
appear", and your form's vba module will appear with a new procedure opening
and closing lines already added. It will look like this:

Sub Private MyButtonName_Click()

End Sub

Between those lines goes my DoCmd....... line from my previous post.
Again, make sure the field names are correct in my line by checking the field
names on each form and comparing those with the names I used in that line.

Hope that helps,
CW

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

:

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW



:

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Krazy Darcy]

yes, the button I am clicking on is in the main part of the artworksdetails
form which is displaying a populated record. artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record.

on art-use:
data> control source = record number
other> Name = record number

on artworksdetails:
data> control source = record number
other> Name = record number

record number is the keyfield for the main artworks table (which form
artworkdetails is tied to) record number is the relationship link between
the artworks table and the art-use table

Is your main form on an existing record when you click on the button to open
the other form?

Does the popup form control name [Record Number] happen to be the exact same
as the field it's bound to? (Read: 'name' on 'other' tab the same as
'control source' on 'data' tab)?

CW

I have even checked the case of the letters in the file and field names. The
button opens the form art-use but doesn't seem to fill in the record number
field in the art-use form.

It's as if it is behaving like it is wanting to display record(s) that has
the record number that is on the artworksdetails form (the form the button is
on)

thanks

Hi Darcy,

Just put that middle line of my code in the on_click event of the button you
are using to open the second form. Do that by opening your main form in
design view, clicking on the button, opening the properties window (f4),
clicking on the 'events' tab, and finding the "on click" event. Double click
in the text area beside the "on_click", and "event procedure" should fill the
blank area. Also, just to the right of that blank area, a button with "..."
on it will appear. Click that button (after you get the "event procedure" to
appear", and your form's vba module will appear with a new procedure opening
and closing lines already added. It will look like this:

Sub Private MyButtonName_Click()

End Sub

Between those lines goes my DoCmd....... line from my previous post.
Again, make sure the field names are correct in my line by checking the field
names on each form and comparing those with the names I used in that line.

Hope that helps,
CW

:

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

:

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW



:

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Cheese_whiz]

"artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record. "

So you're saying the form artworksdetails is already open BEFORE you hit the
button you're trying to code? If so, that may be a problem. I don't think
that DoCmd line I gave you will work if the form is opened already.

If the form is NOT opened already, then I'd try renaming the control
(other>Record Number) on the artworksDetails to something like
txtRecordNumber, then modify my DoCmd line accordingly. Sometimes you get an
ambiguous reference when the control name is the same as the bound field.

If you have one form (call it formA) opening another (formB) and then you
are opening a third (formC) and trying to display records in formB based on
some field in formC, it sounds pretty confusing. If that's the case, I
consider coming up with a method that doesn't have three forms open at once
if possible.

Just to be clear, I'm not sure what the effect of having the form already
opened will have on the DoCmd line, but if it's not working and you try
changing the name of the control as instructed above, and it STILL doesn't
work, then that just about has to be the problem. I tested that line before
I made my original post this morning and I've used it many times before (I
just tested it to make sure the 'me' reference worked).

Hope something here helps.
CW

yes, the button I am clicking on is in the main part of the artworksdetails
form which is displaying a populated record. artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record.

on art-use:
data> control source = record number
other> Name = record number

on artworksdetails:
data> control source = record number
other> Name = record number

record number is the keyfield for the main artworks table (which form
artworkdetails is tied to) record number is the relationship link between
the artworks table and the art-use table

Is your main form on an existing record when you click on the button to open
the other form?

Does the popup form control name [Record Number] happen to be the exact same
as the field it's bound to? (Read: 'name' on 'other' tab the same as
'control source' on 'data' tab)?

CW

I have even checked the case of the letters in the file and field names. The
button opens the form art-use but doesn't seem to fill in the record number
field in the art-use form.

It's as if it is behaving like it is wanting to display record(s) that has
the record number that is on the artworksdetails form (the form the button is
on)

thanks

:

Hi Darcy,

Just put that middle line of my code in the on_click event of the button you
are using to open the second form. Do that by opening your main form in
design view, clicking on the button, opening the properties window (f4),
clicking on the 'events' tab, and finding the "on click" event. Double click
in the text area beside the "on_click", and "event procedure" should fill the
blank area. Also, just to the right of that blank area, a button with "..."
on it will appear. Click that button (after you get the "event procedure" to
appear", and your form's vba module will appear with a new procedure opening
and closing lines already added. It will look like this:

Sub Private MyButtonName_Click()

End Sub

Between those lines goes my DoCmd....... line from my previous post.
Again, make sure the field names are correct in my line by checking the field
names on each form and comparing those with the names I used in that line.

Hope that helps,
CW

:

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

:

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW



:

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Krazy Darcy]

artworksdetails is the form which has the button I am trying to get to work
and as the button is on it is open. that button opens form art-use. It is
also supposed to pass the value that is in "record number" on the currently
open form artworksdetails on to form art-use to fill "record number" on that
form out so I only have to fill out the other fields in form art-use.

As far as I know, how artworksdetails was opened up shouldn't affect this as
when artworksdetails is opened it allways has a record displayed in it so the
record number can never be empty. (This is because it is for displaying all
the details of a particular record as search screens etc only display the
required fields for their purpose and so artworksdetails can be opened from
many differant places)

I have also just changed it so on form art-use I'm using rec-no instead of
record number (both for the control source and name as well as in the code.

this is the bit of code for that button residing on artworksdetails

Private Sub Command85_Click()

DoCmd.OpenForm "art-use", , , "[rec-no] = " & Me.[record number]

End Sub

thanks

"artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record. "

So you're saying the form artworksdetails is already open BEFORE you hit the
button you're trying to code? If so, that may be a problem. I don't think
that DoCmd line I gave you will work if the form is opened already.

If the form is NOT opened already, then I'd try renaming the control
(other>Record Number) on the artworksDetails to something like
txtRecordNumber, then modify my DoCmd line accordingly. Sometimes you get an
ambiguous reference when the control name is the same as the bound field.

If you have one form (call it formA) opening another (formB) and then you
are opening a third (formC) and trying to display records in formB based on
some field in formC, it sounds pretty confusing. If that's the case, I
consider coming up with a method that doesn't have three forms open at once
if possible.

Just to be clear, I'm not sure what the effect of having the form already
opened will have on the DoCmd line, but if it's not working and you try
changing the name of the control as instructed above, and it STILL doesn't
work, then that just about has to be the problem. I tested that line before
I made my original post this morning and I've used it many times before (I
just tested it to make sure the 'me' reference worked).

Hope something here helps.
CW

yes, the button I am clicking on is in the main part of the artworksdetails
form which is displaying a populated record. artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record.

on art-use:
data> control source = record number
other> Name = record number

on artworksdetails:
data> control source = record number
other> Name = record number

record number is the keyfield for the main artworks table (which form
artworkdetails is tied to) record number is the relationship link between
the artworks table and the art-use table

Is your main form on an existing record when you click on the button to open
the other form?

Does the popup form control name [Record Number] happen to be the exact same
as the field it's bound to? (Read: 'name' on 'other' tab the same as
'control source' on 'data' tab)?

CW

:

I have even checked the case of the letters in the file and field names. The
button opens the form art-use but doesn't seem to fill in the record number
field in the art-use form.

It's as if it is behaving like it is wanting to display record(s) that has
the record number that is on the artworksdetails form (the form the button is
on)

thanks

:

Hi Darcy,

Just put that middle line of my code in the on_click event of the button you
are using to open the second form. Do that by opening your main form in
design view, clicking on the button, opening the properties window (f4),
clicking on the 'events' tab, and finding the "on click" event. Double click
in the text area beside the "on_click", and "event procedure" should fill the
blank area. Also, just to the right of that blank area, a button with "..."
on it will appear. Click that button (after you get the "event procedure" to
appear", and your form's vba module will appear with a new procedure opening
and closing lines already added. It will look like this:

Sub Private MyButtonName_Click()

End Sub

Between those lines goes my DoCmd....... line from my previous post.
Again, make sure the field names are correct in my line by checking the field
names on each form and comparing those with the names I used in that line.

Hope that helps,
CW

:

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

:

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW



:

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Cheese_whiz]

"I have also just changed it so on form art-use I'm using rec-no instead of
record number (both for the control source and name as well as in the code. "

The idea was to have the field in the control source for the control (data
tab>control source) and the name of the control (other>name)NOT be the same.
To accomplish that, you could have just renamed the 'name' of the control in
question. If you renamed them both, then you defeated the whole purpose of
that suggestion. In addition, if you renamed the control source and didn't
go into the query/table (or both) and rename the actual field in question,
you won't get any data showing in the control because it will be based on a
field that doesn't exist. Also, if you rename a field in a table after
you've based queries/forms/reports on it, you will no longer get the data you
are looking for.

If I were you, I'd change back the control source to whatever it was, and
any related changes you made (as in changes to the field names in a query or
table). Then I'd try the code again (with the control name "rec-no" and the
control source "record number" on that form).

If that doesn't help, I just don't know what else to try. The code I
originally provided should work. It always has for me, and I've tested it
with two new 'test' buttons since this morning just to make sure I wasn't
going nuts.

Good luck. Sorry I haven't been able to help more.

CW

artworksdetails is the form which has the button I am trying to get to work
and as the button is on it is open. that button opens form art-use. It is
also supposed to pass the value that is in "record number" on the currently
open form artworksdetails on to form art-use to fill "record number" on that
form out so I only have to fill out the other fields in form art-use.

As far as I know, how artworksdetails was opened up shouldn't affect this as
when artworksdetails is opened it allways has a record displayed in it so the
record number can never be empty. (This is because it is for displaying all
the details of a particular record as search screens etc only display the
required fields for their purpose and so artworksdetails can be opened from
many differant places)

I have also just changed it so on form art-use I'm using rec-no instead of
record number (both for the control source and name as well as in the code.

this is the bit of code for that button residing on artworksdetails

Private Sub Command85_Click()

DoCmd.OpenForm "art-use", , , "[rec-no] = " & Me.[record number]

End Sub

thanks

"artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record. "

So you're saying the form artworksdetails is already open BEFORE you hit the
button you're trying to code? If so, that may be a problem. I don't think
that DoCmd line I gave you will work if the form is opened already.

If the form is NOT opened already, then I'd try renaming the control
(other>Record Number) on the artworksDetails to something like
txtRecordNumber, then modify my DoCmd line accordingly. Sometimes you get an
ambiguous reference when the control name is the same as the bound field.

If you have one form (call it formA) opening another (formB) and then you
are opening a third (formC) and trying to display records in formB based on
some field in formC, it sounds pretty confusing. If that's the case, I
consider coming up with a method that doesn't have three forms open at once
if possible.

Just to be clear, I'm not sure what the effect of having the form already
opened will have on the DoCmd line, but if it's not working and you try
changing the name of the control as instructed above, and it STILL doesn't
work, then that just about has to be the problem. I tested that line before
I made my original post this morning and I've used it many times before (I
just tested it to make sure the 'me' reference worked).

Hope something here helps.
CW

yes, the button I am clicking on is in the main part of the artworksdetails
form which is displaying a populated record. artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record.

on art-use:
data> control source = record number
other> Name = record number

on artworksdetails:
data> control source = record number
other> Name = record number

record number is the keyfield for the main artworks table (which form
artworkdetails is tied to) record number is the relationship link between
the artworks table and the art-use table




:

Is your main form on an existing record when you click on the button to open
the other form?

Does the popup form control name [Record Number] happen to be the exact same
as the field it's bound to? (Read: 'name' on 'other' tab the same as
'control source' on 'data' tab)?

CW

:

I have even checked the case of the letters in the file and field names. The
button opens the form art-use but doesn't seem to fill in the record number
field in the art-use form.

It's as if it is behaving like it is wanting to display record(s) that has
the record number that is on the artworksdetails form (the form the button is
on)

thanks

:

Hi Darcy,

Just put that middle line of my code in the on_click event of the button you
are using to open the second form. Do that by opening your main form in
design view, clicking on the button, opening the properties window (f4),
clicking on the 'events' tab, and finding the "on click" event. Double click
in the text area beside the "on_click", and "event procedure" should fill the
blank area. Also, just to the right of that blank area, a button with "..."
on it will appear. Click that button (after you get the "event procedure" to
appear", and your form's vba module will appear with a new procedure opening
and closing lines already added. It will look like this:

Sub Private MyButtonName_Click()

End Sub

Between those lines goes my DoCmd....... line from my previous post.
Again, make sure the field names are correct in my line by checking the field
names on each form and comparing those with the names I used in that line.

Hope that helps,
CW

:

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

:

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW



:

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Krazy Darcy]

Apologies for my slow responce just got home from full day at work here in
New Zealand (gmt +13 = nzdst)

Renaming the field in art-use table and form is not a biggie at this stage
as only the one form refers to it and there is no data in the art-use table
at present.

Here is what I currently have. I included the underlying table names and
name of the relevant field in each

Form art-use

Format>control source = record number
Other>name = rec-no

(table art-use:
field name = record number)

Form artworkdetails
format>control source = record number
other> name = record

(table art works table
field name = record number
((this can't be changed due to amount of queries and forms refer to it))


Currently the Event procedure for button on artworksdetails:

Private Sub Command86_Click()
DoCmd.OpenForm "art-use", , , "[record number] = " & Me.[record]

Art-use is Not a subform.
End Sub

the button is on form artworksdetails and is supposed to open art-use and
pass the value for the record number and insert it in the form art-use (not
filter for it)

While the record number can be entered in by hand it would have been better
to have it done automatically by the system.

Thanks very much for your time.

"I have also just changed it so on form art-use I'm using rec-no instead of
record number (both for the control source and name as well as in the code. "

The idea was to have the field in the control source for the control (data
tab>control source) and the name of the control (other>name)NOT be the same.
To accomplish that, you could have just renamed the 'name' of the control in
question. If you renamed them both, then you defeated the whole purpose of
that suggestion. In addition, if you renamed the control source and didn't
go into the query/table (or both) and rename the actual field in question,
you won't get any data showing in the control because it will be based on a
field that doesn't exist. Also, if you rename a field in a table after
you've based queries/forms/reports on it, you will no longer get the data you
are looking for.

If I were you, I'd change back the control source to whatever it was, and
any related changes you made (as in changes to the field names in a query or
table). Then I'd try the code again (with the control name "rec-no" and the
control source "record number" on that form).

If that doesn't help, I just don't know what else to try. The code I
originally provided should work. It always has for me, and I've tested it
with two new 'test' buttons since this morning just to make sure I wasn't
going nuts.

Good luck. Sorry I haven't been able to help more.

CW

artworksdetails is the form which has the button I am trying to get to work
and as the button is on it is open. that button opens form art-use. It is
also supposed to pass the value that is in "record number" on the currently
open form artworksdetails on to form art-use to fill "record number" on that
form out so I only have to fill out the other fields in form art-use.

As far as I know, how artworksdetails was opened up shouldn't affect this as
when artworksdetails is opened it allways has a record displayed in it so the
record number can never be empty. (This is because it is for displaying all
the details of a particular record as search screens etc only display the
required fields for their purpose and so artworksdetails can be opened from
many differant places)

I have also just changed it so on form art-use I'm using rec-no instead of
record number (both for the control source and name as well as in the code.

this is the bit of code for that button residing on artworksdetails

Private Sub Command85_Click()

DoCmd.OpenForm "art-use", , , "[rec-no] = " & Me.[record number]

End Sub

thanks

"artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record. "

So you're saying the form artworksdetails is already open BEFORE you hit the
button you're trying to code? If so, that may be a problem. I don't think
that DoCmd line I gave you will work if the form is opened already.

If the form is NOT opened already, then I'd try renaming the control
(other>Record Number) on the artworksDetails to something like
txtRecordNumber, then modify my DoCmd line accordingly. Sometimes you get an
ambiguous reference when the control name is the same as the bound field.

If you have one form (call it formA) opening another (formB) and then you
are opening a third (formC) and trying to display records in formB based on
some field in formC, it sounds pretty confusing. If that's the case, I
consider coming up with a method that doesn't have three forms open at once
if possible.

Just to be clear, I'm not sure what the effect of having the form already
opened will have on the DoCmd line, but if it's not working and you try
changing the name of the control as instructed above, and it STILL doesn't
work, then that just about has to be the problem. I tested that line before
I made my original post this morning and I've used it many times before (I
just tested it to make sure the 'me' reference worked).

Hope something here helps.
CW



:

yes, the button I am clicking on is in the main part of the artworksdetails
form which is displaying a populated record. artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record.

on art-use:
data> control source = record number
other> Name = record number

on artworksdetails:
data> control source = record number
other> Name = record number

record number is the keyfield for the main artworks table (which form
artworkdetails is tied to) record number is the relationship link between
the artworks table and the art-use table




:

Is your main form on an existing record when you click on the button to open
the other form?

Does the popup form control name [Record Number] happen to be the exact same
as the field it's bound to? (Read: 'name' on 'other' tab the same as
'control source' on 'data' tab)?

CW

:

I have even checked the case of the letters in the file and field names. The
button opens the form art-use but doesn't seem to fill in the record number
field in the art-use form.

It's as if it is behaving like it is wanting to display record(s) that has
the record number that is on the artworksdetails form (the form the button is
on)

thanks

:

Hi Darcy,

Just put that middle line of my code in the on_click event of the button you
are using to open the second form. Do that by opening your main form in
design view, clicking on the button, opening the properties window (f4),
clicking on the 'events' tab, and finding the "on click" event. Double click
in the text area beside the "on_click", and "event procedure" should fill the
blank area. Also, just to the right of that blank area, a button with "..."
on it will appear. Click that button (after you get the "event procedure" to
appear", and your form's vba module will appear with a new procedure opening
and closing lines already added. It will look like this:

Sub Private MyButtonName_Click()

End Sub

Between those lines goes my DoCmd....... line from my previous post.
Again, make sure the field names are correct in my line by checking the field
names on each form and comparing those with the names I used in that line.

Hope that helps,
CW

:

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

:

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW



:

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Cheese_whiz]

On the on_click of the button, put this:

YourButton_Click()
DoCmd.OpenForm "art-use", acnormal, , , acFormAdd
End Sub

(access will add the first and last line of the above)

Then, open the form art-use in design mode, click on the field (rec-no)
where you want the data to appear, open the properties window (f4), click on
the data tab, then in the space beside "default value", add this:

=Forms!artworksdetails.[record]

That will work fine as long as you are opening the art-use form when the
artworksdetail form is opened (eg with the button you just coded). I could
probably come up with a way that would handle situations where the form was
opened without the artworksdetails form already being opened, if need be.

Hope that helps,
CW

Apologies for my slow responce just got home from full day at work here in
New Zealand (gmt +13 = nzdst)

Renaming the field in art-use table and form is not a biggie at this stage
as only the one form refers to it and there is no data in the art-use table
at present.

Here is what I currently have. I included the underlying table names and
name of the relevant field in each

Form art-use

Format>control source = record number
Other>name = rec-no

(table art-use:
field name = record number)

Form artworkdetails
format>control source = record number
other> name = record

(table art works table
field name = record number
((this can't be changed due to amount of queries and forms refer to it))


Currently the Event procedure for button on artworksdetails:

Private Sub Command86_Click()
DoCmd.OpenForm "art-use", , , "[record number] = " & Me.[record]

Art-use is Not a subform.
End Sub

the button is on form artworksdetails and is supposed to open art-use and
pass the value for the record number and insert it in the form art-use (not
filter for it)

While the record number can be entered in by hand it would have been better
to have it done automatically by the system.

Thanks very much for your time.

"I have also just changed it so on form art-use I'm using rec-no instead of
record number (both for the control source and name as well as in the code. "

The idea was to have the field in the control source for the control (data
tab>control source) and the name of the control (other>name)NOT be the same.
To accomplish that, you could have just renamed the 'name' of the control in
question. If you renamed them both, then you defeated the whole purpose of
that suggestion. In addition, if you renamed the control source and didn't
go into the query/table (or both) and rename the actual field in question,
you won't get any data showing in the control because it will be based on a
field that doesn't exist. Also, if you rename a field in a table after
you've based queries/forms/reports on it, you will no longer get the data you
are looking for.

If I were you, I'd change back the control source to whatever it was, and
any related changes you made (as in changes to the field names in a query or
table). Then I'd try the code again (with the control name "rec-no" and the
control source "record number" on that form).

If that doesn't help, I just don't know what else to try. The code I
originally provided should work. It always has for me, and I've tested it
with two new 'test' buttons since this morning just to make sure I wasn't
going nuts.

Good luck. Sorry I haven't been able to help more.

CW

artworksdetails is the form which has the button I am trying to get to work
and as the button is on it is open. that button opens form art-use. It is
also supposed to pass the value that is in "record number" on the currently
open form artworksdetails on to form art-use to fill "record number" on that
form out so I only have to fill out the other fields in form art-use.

As far as I know, how artworksdetails was opened up shouldn't affect this as
when artworksdetails is opened it allways has a record displayed in it so the
record number can never be empty. (This is because it is for displaying all
the details of a particular record as search screens etc only display the
required fields for their purpose and so artworksdetails can be opened from
many differant places)

I have also just changed it so on form art-use I'm using rec-no instead of
record number (both for the control source and name as well as in the code.

this is the bit of code for that button residing on artworksdetails

Private Sub Command85_Click()

DoCmd.OpenForm "art-use", , , "[rec-no] = " & Me.[record number]

End Sub

thanks

:

"artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record. "

So you're saying the form artworksdetails is already open BEFORE you hit the
button you're trying to code? If so, that may be a problem. I don't think
that DoCmd line I gave you will work if the form is opened already.

If the form is NOT opened already, then I'd try renaming the control
(other>Record Number) on the artworksDetails to something like
txtRecordNumber, then modify my DoCmd line accordingly. Sometimes you get an
ambiguous reference when the control name is the same as the bound field.

If you have one form (call it formA) opening another (formB) and then you
are opening a third (formC) and trying to display records in formB based on
some field in formC, it sounds pretty confusing. If that's the case, I
consider coming up with a method that doesn't have three forms open at once
if possible.

Just to be clear, I'm not sure what the effect of having the form already
opened will have on the DoCmd line, but if it's not working and you try
changing the name of the control as instructed above, and it STILL doesn't
work, then that just about has to be the problem. I tested that line before
I made my original post this morning and I've used it many times before (I
just tested it to make sure the 'me' reference worked).

Hope something here helps.
CW



:

yes, the button I am clicking on is in the main part of the artworksdetails
form which is displaying a populated record. artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record.

on art-use:
data> control source = record number
other> Name = record number

on artworksdetails:
data> control source = record number
other> Name = record number

record number is the keyfield for the main artworks table (which form
artworkdetails is tied to) record number is the relationship link between
the artworks table and the art-use table




:

Is your main form on an existing record when you click on the button to open
the other form?

Does the popup form control name [Record Number] happen to be the exact same
as the field it's bound to? (Read: 'name' on 'other' tab the same as
'control source' on 'data' tab)?

CW

:

I have even checked the case of the letters in the file and field names. The
button opens the form art-use but doesn't seem to fill in the record number
field in the art-use form.

It's as if it is behaving like it is wanting to display record(s) that has
the record number that is on the artworksdetails form (the form the button is
on)

thanks

:

Hi Darcy,

Just put that middle line of my code in the on_click event of the button you
are using to open the second form. Do that by opening your main form in
design view, clicking on the button, opening the properties window (f4),
clicking on the 'events' tab, and finding the "on click" event. Double click
in the text area beside the "on_click", and "event procedure" should fill the
blank area. Also, just to the right of that blank area, a button with "..."
on it will appear. Click that button (after you get the "event procedure" to
appear", and your form's vba module will appear with a new procedure opening
and closing lines already added. It will look like this:

Sub Private MyButtonName_Click()

End Sub

Between those lines goes my DoCmd....... line from my previous post.
Again, make sure the field names are correct in my line by checking the field
names on each form and comparing those with the names I used in that line.

Hope that helps,
CW

:

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

:

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW



:

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.

 

[Krazy Darcy]

Thank you very very much for your help.

The codes you posed in your last post worked great.

Just applied it while waiting for my screen to warm up - I don't use
photoshop untill the screen has been on for at least 1/2 pref 1 hour.

I will print the email out for future referance as I will be needing to
apply similar functionality elsewhere. Looking at it I can see how to alter
the form and field names to suit whatever form pair I use it for.

On the on_click of the button, put this:

YourButton_Click()
DoCmd.OpenForm "art-use", acnormal, , , acFormAdd
End Sub

(access will add the first and last line of the above)

Then, open the form art-use in design mode, click on the field (rec-no)
where you want the data to appear, open the properties window (f4), click on
the data tab, then in the space beside "default value", add this:

=Forms!artworksdetails.[record]

That will work fine as long as you are opening the art-use form when the
artworksdetail form is opened (eg with the button you just coded). I could
probably come up with a way that would handle situations where the form was
opened without the artworksdetails form already being opened, if need be.

Hope that helps,
CW

Apologies for my slow responce just got home from full day at work here in
New Zealand (gmt +13 = nzdst)

Renaming the field in art-use table and form is not a biggie at this stage
as only the one form refers to it and there is no data in the art-use table
at present.

Here is what I currently have. I included the underlying table names and
name of the relevant field in each

Form art-use

Format>control source = record number
Other>name = rec-no

(table art-use:
field name = record number)

Form artworkdetails
format>control source = record number
other> name = record

(table art works table
field name = record number
((this can't be changed due to amount of queries and forms refer to it))


Currently the Event procedure for button on artworksdetails:

Private Sub Command86_Click()
DoCmd.OpenForm "art-use", , , "[record number] = " & Me.[record]

Art-use is Not a subform.
End Sub

the button is on form artworksdetails and is supposed to open art-use and
pass the value for the record number and insert it in the form art-use (not
filter for it)

While the record number can be entered in by hand it would have been better
to have it done automatically by the system.

Thanks very much for your time.

"I have also just changed it so on form art-use I'm using rec-no instead of
record number (both for the control source and name as well as in the code. "

The idea was to have the field in the control source for the control (data
tab>control source) and the name of the control (other>name)NOT be the same.
To accomplish that, you could have just renamed the 'name' of the control in
question. If you renamed them both, then you defeated the whole purpose of
that suggestion. In addition, if you renamed the control source and didn't
go into the query/table (or both) and rename the actual field in question,
you won't get any data showing in the control because it will be based on a
field that doesn't exist. Also, if you rename a field in a table after
you've based queries/forms/reports on it, you will no longer get the data you
are looking for.

If I were you, I'd change back the control source to whatever it was, and
any related changes you made (as in changes to the field names in a query or
table). Then I'd try the code again (with the control name "rec-no" and the
control source "record number" on that form).

If that doesn't help, I just don't know what else to try. The code I
originally provided should work. It always has for me, and I've tested it
with two new 'test' buttons since this morning just to make sure I wasn't
going nuts.

Good luck. Sorry I haven't been able to help more.

CW

:

artworksdetails is the form which has the button I am trying to get to work
and as the button is on it is open. that button opens form art-use. It is
also supposed to pass the value that is in "record number" on the currently
open form artworksdetails on to form art-use to fill "record number" on that
form out so I only have to fill out the other fields in form art-use.

As far as I know, how artworksdetails was opened up shouldn't affect this as
when artworksdetails is opened it allways has a record displayed in it so the
record number can never be empty. (This is because it is for displaying all
the details of a particular record as search screens etc only display the
required fields for their purpose and so artworksdetails can be opened from
many differant places)

I have also just changed it so on form art-use I'm using rec-no instead of
record number (both for the control source and name as well as in the code.

this is the bit of code for that button residing on artworksdetails

Private Sub Command85_Click()

DoCmd.OpenForm "art-use", , , "[rec-no] = " & Me.[record number]

End Sub

thanks

:

"artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record. "

So you're saying the form artworksdetails is already open BEFORE you hit the
button you're trying to code? If so, that may be a problem. I don't think
that DoCmd line I gave you will work if the form is opened already.

If the form is NOT opened already, then I'd try renaming the control
(other>Record Number) on the artworksDetails to something like
txtRecordNumber, then modify my DoCmd line accordingly. Sometimes you get an
ambiguous reference when the control name is the same as the bound field.

If you have one form (call it formA) opening another (formB) and then you
are opening a third (formC) and trying to display records in formB based on
some field in formC, it sounds pretty confusing. If that's the case, I
consider coming up with a method that doesn't have three forms open at once
if possible.

Just to be clear, I'm not sure what the effect of having the form already
opened will have on the DoCmd line, but if it's not working and you try
changing the name of the control as instructed above, and it STILL doesn't
work, then that just about has to be the problem. I tested that line before
I made my original post this morning and I've used it many times before (I
just tested it to make sure the 'me' reference worked).

Hope something here helps.
CW



:

yes, the button I am clicking on is in the main part of the artworksdetails
form which is displaying a populated record. artworksdetails was opened from
a another form (in this instance all-cg-sorted-by-year-date) using a similar
method to open artworksdetails to display the full record.

on art-use:
data> control source = record number
other> Name = record number

on artworksdetails:
data> control source = record number
other> Name = record number

record number is the keyfield for the main artworks table (which form
artworkdetails is tied to) record number is the relationship link between
the artworks table and the art-use table




:

Is your main form on an existing record when you click on the button to open
the other form?

Does the popup form control name [Record Number] happen to be the exact same
as the field it's bound to? (Read: 'name' on 'other' tab the same as
'control source' on 'data' tab)?

CW

:

I have even checked the case of the letters in the file and field names. The
button opens the form art-use but doesn't seem to fill in the record number
field in the art-use form.

It's as if it is behaving like it is wanting to display record(s) that has
the record number that is on the artworksdetails form (the form the button is
on)

thanks

:

Hi Darcy,

Just put that middle line of my code in the on_click event of the button you
are using to open the second form. Do that by opening your main form in
design view, clicking on the button, opening the properties window (f4),
clicking on the 'events' tab, and finding the "on click" event. Double click
in the text area beside the "on_click", and "event procedure" should fill the
blank area. Also, just to the right of that blank area, a button with "..."
on it will appear. Click that button (after you get the "event procedure" to
appear", and your form's vba module will appear with a new procedure opening
and closing lines already added. It will look like this:

Sub Private MyButtonName_Click()

End Sub

Between those lines goes my DoCmd....... line from my previous post.
Again, make sure the field names are correct in my line by checking the field
names on each form and comparing those with the names I used in that line.

Hope that helps,
CW

:

I cut/paste put that code in the expression builder (I think its called where
you can edit the vb code).

It opend the art-use form (I did add the - in the form name in the code as
the acxtual form has it in it's name). But it didn't fill the record number
field.
The art-use form is a seperate window not a embedded sub form.

:

Sub Private cmdOpenArtUse_Click()

DoCmd.OpenForm "Art Use", , , "[Record Number] = " & Me.[Record Number]

End Sub

I think that should work.

CW



:

Please could I have a "dummies guide" to opening subform with new record
using a value from another open form when I click on a button on that form.

I have searched the forums and couldn't figure out the various instructions
(I'm not a programmer/IT expert)

I have a form called Artworksdetails. On it is a field calle (without
quotes) "record number".
What I want is when I click on a button it opens the form called art-use on
a new record and pass the value in "record number" (in Artworksdetails) to
the field "record number" in art-use.

Apologies for what seems to be a common subject question but I need it
explained in "laymans terms"

Thank you for your patience.