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.
LVL 1
WSStudentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can set default value of field Sou to the field txtbxSou and all new records will have this value
0
WSStudentAuthor 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.
0
Gustav BrockCIOCommented:
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
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

WSStudentAuthor 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?
0
Gustav BrockCIOCommented:
That's because the textbox isn't updated.
So either move (tab) to another control, or update the form by pressing Shift+Enter .

/gustav
0
WSStudentAuthor 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.
0
Gustav BrockCIOCommented:
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
0
WSStudentAuthor Commented:
Again the same with button also. When click once it change the field value and the next time it's not working.
0
Gustav BrockCIOCommented:
Oh, you may need a MoveFirst:

Dim rs As DAO.Recordset

Set rs = Me!NameOfYourSubformControl.Form.RecordsetClone

If rs.RecordCount > 0 Then
    rs.MoveFirst
End If

While Not rs.EOF
    rs.Edit
        rs!Sou.Value = Me!txtbxSou.Value
    rs.Update
    rs.MoveNext
Wend

rs.Close

Open in new window

/gustav
0
WSStudentAuthor 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?
0
Gustav BrockCIOCommented:
Yes, in that other form have:

Private Sub ButtonCopy_Click()

    Dim frm     As Form
    
    Set frm = Forms("YourForm")
    frm!txtbxSou.Value = somevalue    
    frm.UpdateSou
    
End Sub

Open in new window

Move the current code to a separate function:

Public Sub UpdateSou()

Dim rs As DAO.Recordset

Set rs = Me!NameOfYourSubformControl.Form.RecordsetClone

If rs.RecordCount > 0 Then
    rs.MoveFirst
End If

While Not rs.EOF
    rs.Edit
        rs!Sou.Value = Me!txtbxSou.Value
    rs.Update
    rs.MoveNext
Wend

rs.Close

End Sub

Open in new window

Also, call this sub from your current button click.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WSStudentAuthor 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?
0
Gustav BrockCIOCommented:
Well, either you haven't carried over the code in perfect condition, or your real data has some peculiarities that are not reflected in your test data.

First thing would be to locate where it stops. Set a Pause point (F9) and step line by line.

/gustav
0
WSStudentAuthor 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.
0
WSStudentAuthor 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?
0
Gustav BrockCIOCommented:
You could locate that record and only update this not using a loop, but I'm afraid that it would help much. That "something else going on" you will have to get fixed. If not, it's the kind of issue that may bite you later.

/gustav
0
WSStudentAuthor 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.
0
Gustav BrockCIOCommented:
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
0
WSStudentAuthor Commented:
This code is just updating the first record, it's not updating the selected record.
0
Gustav BrockCIOCommented:
You didn't mention that is was the current record to update. If so:

Public Sub UpdateSou()

    Dim rs As DAO.Recordset

    Set rs = Me!NameOfYourSubformControl.Form.RecordsetClone

    If rs.RecordCount > 0 Then
        rs.Bookmark = Me!NameOfYourSubformControl.Form.Bookmark
        rs.Edit
            rs!Sou.Value = Me!txtbxSou.Value
        rs.Update
    End If
    rs.Close

End Sub

Open in new window

/gustav
0
WSStudentAuthor Commented:
Thank you Gustav, it perfect. Thank you again!
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.