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.

als315

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

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

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

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.

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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?

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

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?

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

ASKER

This code is just updating the first record, it's not updating the selected record.

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thank you Gustav, it perfect. Thank you again!

Gustav Brock

You are welcome!

/gustav