We help IT Professionals succeed at work.

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

WS
WS asked
on
622 Views
Last Modified: 2018-02-02
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.
Comment
Watch Question

CERTIFIED EXPERT

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

Author

Commented:
@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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
WSStudent

Author

Commented:
@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?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

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

/gustav
WSStudent

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
WSStudent

Author

Commented:
Again the same with button also. When click once it change the field value and the next time it's not working.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
WSStudent

Author

Commented:
@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?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
WSStudent

Author

Commented:
@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?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
WSStudent

Author

Commented:
@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.
WSStudent

Author

Commented:
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?
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
WSStudent

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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
WSStudent

Author

Commented:
This code is just updating the first record, it's not updating the selected record.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
WSStudent

Author

Commented:
Thank you Gustav, it perfect. Thank you again!
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.