We help IT Professionals succeed at work.

access 2007 populate memo box with data from another table

hi all,

i thought i knew how to do this but it seems not... ive tried messing around with the default value to make this work but no cigar as yet.

i'd like the memo text area on my for to be pre-populated for the user when they open up a new form with data from another table. however the user is then free to make  changes to that data and then click save which saves to the other table.

if the user decides to open the data they just saved then they should see the edited version of textarea data and not 'default' data

i hope of explained that ok.

as always, all help apprecited.

zac
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2013
Commented:
Try this in the Current event of your form:

Private Sub Form_Current()
    if me.newrecord = true then
           Me.MyTextbox = DLookup("SomeField", "YourTable", "Some Optional criteria")
    end if
end sub

Open in new window

Most Valuable Expert 2012
Top Expert 2013
Commented:
That would give you the default value.

To 'save' it -- in your 'save' button's click event:


Dim strSQL as string
strSQL = "UPDATE YourTable SET YourField = " &  chr(34) & me.MyTextbox & chr(34)
CurrentDB.Execute strSQL, dbFailOnError

Open in new window

Author

Commented:
ok i'm using this:

Private Sub Form_frmNewQA_Current()
    If Me.NewRecord = True Then
           Me.QATerms = DLookup("QATermsDefault", "QA_Defaults")
    End If
End Sub
the "QA_Defaults" bit is the name of the tble right?

nothing seems to happen. note how i added the name of the form in the private sub line (frmNewQA) is that correct?

Most Valuable Expert 2012
Top Expert 2013
Commented:
No... The Current event is a built-in event.

In the form's design view, click the ... next to On Current.

Then select Code Builder.

Place these lines between the Sub and End Sub lines:

    If Me.NewRecord = True Then
           Me.QATerms = DLookup("QATermsDefault", "QA_Defaults")
    End If

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
(You shouldn't rename the current event)

Author

Commented:
ok got ya.

if i use this:

Private Sub Form_Current()
    If Me.NewRecord = True Then
           Me.QATerms = DLookup("QATermsDefault", "tblQA_Defaults")
    End If
End Sub

i get:

 error

Author

Commented:
oh wait!!!

how many on current event can i have?

i already have onee:

Private Sub Form_Current()
If Me![Check11] = 1 Then
Me![QASubjectTo1].Visible = True
Me![Check14].Visible = True
Else
Me![QASubjectTo1].Visible = False
Me![Check14].Visible = False
Me![QASubjectTo2].Visible = False
Me![Check17].Visible = False
Me![QASubjectTo3].Visible = False
Me![Check20].Visible = False
Me![QASubjectTo4].Visible = False
Me![Check23].Visible = False
Me![QASubjectTo5].Visible = False
End If
End Sub
Most Valuable Expert 2012
Top Expert 2013

Commented:
Okay - I think that means that you have a duplicate Current Event now (that confuses Access).

Remove the initial attempt

Most Valuable Expert 2012
Top Expert 2013
Commented:
<< how many on current event can i have? >>

Just one per form.

The code I gave you should be ADDED to your existing code.

Author

Commented:
see post above

is there a way round this?
Most Valuable Expert 2012
Top Expert 2013

Commented:
We're crossing posts...

You need to add the code I gave you to your existing Current Event code.  Place it at the beginning.

Author

Commented:
yey i think we're gettin there. it only seems to appear when i click on the new form button and not when the form is first opened. i guess i could just add the same code under the on load event?

ok, now to work on your second bit of code your gave me.
Most Valuable Expert 2012
Top Expert 2013

Commented:
I thought the idea was to ONLY do this for new records.

If you want to load this everytime the form opens, just take out the new record check.

In other words, reduce this:

    If Me.NewRecord = True Then
           Me.QATerms = DLookup("QATermsDefault", "tblQA_Defaults")
    End If

Open in new window



To this:

 
          Me.QATerms = DLookup("QATermsDefault", "tblQA_Defaults")

Open in new window


(Keeping it in the Current Event)

Author

Commented:
NOPE SORRY SCRAP THAT

i was getting my self mixed up, i had blank records saved in the table, i was just generally getting confused.

its all working thanks for your excellent help.

Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad to help :-)