Avatar of WS
WS
Flag for United Arab Emirates asked on

MS Access - Form - Populate subform field based on main form textbox

Hi,

I have a form that import data from excel.The main form has a text box name as “txtbxSou” in which user will enter data. There is subform as “sfrmImport”which have field as “Sou”(empty when data is import). I want that when user enter value in text box on main form all the record in subform take that value in field “Sou”. For example 10 record are import the field Sou would be empty, then user will enter some value say “15” in main form text box then the field Sou in subform should get 15 in all the ten record that are import.

Any help how to make this work?

Thank you.
Microsoft AccessVBA

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
als315

You can set default value of field Sou to the field txtbxSou and all new records will have this value
WS

ASKER
@als315, that is for all the new record that will be entered. i want to add this value to the record that are already there in the temporary table through import.
Gustav Brock

Run this in the AfterUpdate event of txtbxSou:

Dim rs As DAO.Recordset

Set rs = Me!NameOfYourSubformControl.Form.RecordsetClone
While Not rs.EOF
    rs.Edit
        rs!Sou.Value = Me!txtbxSou.Value
    rs.Update
    rs.MoveNext
Wend
rs.Close

Open in new window

/gustav
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
WS

ASKER
@Gustav, this is somehow what i want but there are 2 problem with this code:

1. Only it edit all the exsisting record once For example: if i have enter "6" in text box it will work perfectly changing field value to "6" but then again when i write "10" instead of "6" it's not working.

2. I have to click on subform field "Sou" in the subform to update the value, if i don't click on subform "Sou" field it doesn't change value.

Can you help me in this?
Gustav Brock

That's because the textbox isn't updated.
So either move (tab) to another control, or update the form by pressing Shift+Enter .

/gustav
WS

ASKER
How to update textbox?

How to move tab to another control? Shift+Enter is also not working and even i can't use it as the user won't be able to click on Shift+Enter everytime.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Gustav Brock

If you have no update event, you can have a button to click. Then use the OnClick event of this to call the update code.

/gustav
WS

ASKER
Again the same with button also. When click once it change the field value and the next time it's not working.
SOLUTION
Gustav Brock

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
WS

ASKER
@Gustav, yes now this is working fine. But i came across another problem due to which i change my approach a bit. The code is the same, it;s just instead of user entering the value i will get value from another form in this textbox (txtbxSou), rest is the same. Is there a way that when i get value from another form in textbox it automatically update the subform instead of clicking on button? for example in the other form i click on button and it add value "12" in the textbxSou and this value "12" updates the subform field Sou instead of button click?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
WS

ASKER
@Gustav, i know your code is fine as it is working in my example database perfectly but in my operational DB , when i try to run this code Access stop working, do you have any about that?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
WS

ASKER
@Gustav, Is there a way using this code to update single record instead of all in Sou field? if that is possible that will also fulfill the requirement. In this case Access is continuously crashing.
WS

ASKER
i have carried the same code but in stepping in code also it's not showing anything, there is some weird thing going on and i am not able to figure out, what about the other thing to edit single instead of all record? is there a way for that through this code?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
WS

ASKER
i totally agree with the thing that it will bite later and for sure i will fix this, but i have a deadline due to that reason i want to go for the other approach which somehow is not the good one but will fulfill and then later i can fix this one. As this form have a lot of other things, if i go on to fix this today the remaining forms will be left.The other approach locate the record and update. I know how to update but can you let me know how to locate the record?

Thank you.
Gustav Brock

It could be something like:

Public Sub UpdateSou()

    Dim rs As DAO.Recordset

    Set rs = Me!NameOfYourSubformControl.Form.RecordsetClone

    If rs.RecordCount > 0 Then
        rs.FindFirst "SomeField = '" & SomeValue & "'"    ' No single-quotes if value is numeric.
        If rs.NoMatch = False Then
            rs.Edit
                rs!Sou.Value = Me!txtbxSou.Value
            rs.Update
        End If
    End If
    rs.Close

End Sub

Open in new window

/gustav
WS

ASKER
This code is just updating the first record, it's not updating the selected record.
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
WS

ASKER
Thank you Gustav, it perfect. Thank you again!
Gustav Brock

You are welcome!

/gustav