How to update subform using unbound text box on main form (1 Viewer)

ausajid

New member
Local time
Today, 11:54
Joined
May 9, 2015
Messages
17
hi,

i have a subform on main form, i have unbound text box on main form where if i add any value, goes to first row of [orderno] in the subform (datasheet view). what i want to fill in the entire column of [onorder] instead of just first row.

(this is my code at afterupdate event (Me.PO_Query_subform!orderno = Me.maintextboxname))

any help will be very much appreciated.

regards.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:54
Joined
Oct 29, 2018
Messages
21,358
Use an UPDATE query instead.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
42,971
what i want to fill in the entire column of [onorder] instead of just first row.
If that is the case, then the field is in the wrong table. It belongs in the parent table.
if i add any value, goes to first row of [orderno]
Just FYI, When you address a form/subform from a different form, you ALWAYS reference the CURRENT record only.
 

ausajid

New member
Local time
Today, 11:54
Joined
May 9, 2015
Messages
17
If that is the case, then the field is in the wrong table. It belongs in the parent table.

Just FYI, When you address a form/subform from a different form, you ALWAYS reference the CURRENT record only.
hi, thank you for your replies, but is there any way to update the entire column in the subform using vba or macros?

thanks in advance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:54
Joined
Oct 29, 2018
Messages
21,358
hi, thank you for your replies, but is there any way to update the entire column in the subform using vba or macros?

thanks in advance.
Like I said earlier, you can use an UPDATE query, which you can execute using vba or macro.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
42,971
hi, thank you for your replies, but is there any way to update the entire column in the subform using vba or macros?
theDBguy told you how to do it in #2 and again in #5 but if your tables were properly normalized, you wouldn't need to do it at all.

To create an update query, open the QBE (query builder). Select the column you want to update, select the FK.
In the Criteria field of the FK, reference the main form
Forms!myform!myFK
Change the query type to Update
Now cgabge tge UpdateTo cell under the column you want to update to get the data from the form
Forms!myform!SomeValue

Save the query
Add the code to run it.

But, again, it will be far better in the long run to reexamine your table schema.
 

ausajid

New member
Local time
Today, 11:54
Joined
May 9, 2015
Messages
17
hi,

thank you for your advice. i have tried the update query but when i run the query i am getting the message "enter parameter value Forms!PO_Query_subform.orderno"

any more advise will be appreciated.

thanks in advance.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
42,971
Forms!PO_Query_subform.orderno

The dot needs to be a bang

Forms!PO_Query_subform!orderno

VBA is flexible in some cases with how you reference objects, SQL, not so much.
 

ausajid

New member
Local time
Today, 11:54
Joined
May 9, 2015
Messages
17
Thank you Pat Hartman,

tried with ! but the result is same. no good luck. any thing else i can do to sort this out, please advise.

regards.

sajid
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
42,971
The message indicates a typo. Access can't find the the object you referenced. Make sure everything is spelled correctly. Also, you can take advantage of intellisense. Open the QBE and delete the reference to that field and type it again. Access will help you.
 

ausajid

New member
Local time
Today, 11:54
Joined
May 9, 2015
Messages
17
thank you Pat, but some thing is still not letting me sort this out. i have made a test database and attached herewith. when you open the form1, you will see just two lines in the subform. in the main form test (unbound), i need to add any value in the box which i need to post in the subform. please check and advise what is wrong in the db.

i will much appreciate for your kind help.

regards.
 

Attachments

  • Database1.accdb
    1.1 MB · Views: 245

Pat Hartman

Super Moderator
Staff member
Local time
Today, 14:54
Joined
Feb 19, 2002
Messages
42,971
The database you posted is corrupted. There are no filters or criteria and yet the subform is showing only two records

Also, the update query has criteria that doesn't make sense.

And, I'll remind you again, if you need to do this, your tables are not properly defined.
 
Last edited:

ausajid

New member
Local time
Today, 11:54
Joined
May 9, 2015
Messages
17
hi, pat

as i have very limited knowledge of db, can you please make me a test db like this so i can see where i a making mistakes.

thanks in advance.

best regards.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:54
Joined
May 7, 2009
Messages
19,169
is this what you have in mind.
 

Attachments

  • Database1 (4).accdb
    428 KB · Views: 217

ausajid

New member
Local time
Today, 11:54
Joined
May 9, 2015
Messages
17
hi,

thank you for attachment, unfortunately i am not able to open the file, i have 2003 version on my laptop, can you please save as for 2003 and resend.

i will appreciated.

regards.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:54
Joined
May 7, 2009
Messages
19,169
you have a2003? then how come you managed to upload an .accdb file (post#11)?
only .mdb is available in a2003.
 

ausajid

New member
Local time
Today, 11:54
Joined
May 9, 2015
Messages
17
hi,

sorry i have 2007 version installed in my laptop, can you save as for 2007 and send it again.

thanks in advance.
 

Users who are viewing this thread

Top Bottom