How to link main form to memo field in another form?

[supernova]

I have an invoice table and a continuous form which shows all invoice numbers, delivery date etc. I have a notes field in this table which I don't want to put in the continuous form. Instead, I want to be able to double click on the invoice number and another form pops up where I can write my notes in it which I want to use in a report later on and the notes I type in are stored in the invoice table. Is there a way to connect these two forms together without any vba?

Thanks in advance

 

[bob fitz]

I don't know of any way to do this without using a little vba. Why is it necessary to do it without.

 

[bob fitz]

It may be possible using macros but I don't use them so can't be sure about that.

 

[supernova]

I don't know of any way to do this without using a little vba. Why is it necessary to do it without.

Because I don't know vba but I think a little bit of vba will not kill me . What would be your approach?

 

[bob fitz]

Create your Popup form on a query which has the Primary Key field and the memo field. In the criteria row of the Primary Key field enter an expression that refers to the text box on the first form which holds the Primary Key value. The expression would be something like:
[Forms]![frmFirstForm]![PKID]
frmFirstForm is the name of the first form and PKID is the text box name.
Set the Pop Up property of the second form to Yes.

Use the following two lines of code in the On Double Click event of a control (probably a text box) on the first form:

    DoCmd.OpenForm "[COLOR="Red"]frmMemo[/COLOR]", , , "[COLOR="red"]ID[/COLOR]=" & Me.[COLOR="Blue"]ID[/COLOR]
    Forms.[COLOR="Red"]frmMemo[/COLOR].Requery

where frmMemo is the name of the second form,
where ID is the name of the Primary Key field and where ID is the name of the control on the first form which holds the Primary Key value.

 

[arnelgp]

you have to use vba.

drag your note field in your continuous form , make its Visible property to No, so that it will not show.

now create a pop, modal form where you will type the note.
insert an unbound textbox for the note (let say, yourTextBoxNameInPopUpForm).
drag a command button on the pop form will close the form.
program the close button's click event:

dim strReturn As String

private sub button_click()
'save the value of our note
strReturn = Me yourTextBoxNameInPopUpForm
Call Form_Close
end sub

write code on the pop-up form's close event:

private sub form_close()
'put the value of the textbox in pop to note field in your continuos form
Forms![yourContinousFormName]!yourNoteControlName = strReturn
end sub

put event on the Load event of the pop form, so that it will get the value of the notes from your continuos form:

private sub form_load()
'put the value of notes field from continous form to this pop form\
strReturn = Forms![yourContinousFormName]!yourNoteControlName
'assign the note (if there is) to the textbox in the pop form
Me yourTextBoxNameInPopUpForm = strReturn
end sub

 

[supernova]

Create your Popup form on a query which has the Primary Key field and the memo field. In the criteria row of the Primary Key field enter an expression that refers to the text box on the first form which holds the Primary Key value. The expression would be something like:
[Forms]![frmFirstForm]![PKID]
frmFirstForm is the name of the first form and PKID is the text box name.
Set the Pop Up property of the second form to Yes.

Use the following two lines of code in the On Double Click event of a control (probably a text box) on the first form:

    DoCmd.OpenForm "[COLOR=Red]frmMemo[/COLOR]", , , "[COLOR=red]ID[/COLOR]=" & Me.[COLOR=Blue]ID[/COLOR]
    Forms.[COLOR=Red]frmMemo[/COLOR].Requery

where frmMemo is the name of the second form,
where ID is the name of the Primary Key field and where ID is the name of the control on the first form which holds the Primary Key value.

Thank you so much. It worked great.

 

[supernova]

you have to use vba.

drag your note field in your continuous form , make its Visible property to No, so that it will not show.

now create a pop, modal form where you will type the note.
insert an unbound textbox for the note (let say, yourTextBoxNameInPopUpForm).
drag a command button on the pop form will close the form.
program the close button's click event:

dim strReturn As String

private sub button_click()
'save the value of our note
strReturn = Me yourTextBoxNameInPopUpForm
Call Form_Close
end sub

write code on the pop-up form's close event:

private sub form_close()
'put the value of the textbox in pop to note field in your continuos form
Forms![yourContinousFormName]!yourNoteControlName = strReturn
end sub

put event on the Load event of the pop form, so that it will get the value of the notes from your continuos form:

private sub form_load()
'put the value of notes field from continous form to this pop form\
strReturn = Forms![yourContinousFormName]!yourNoteControlName
'assign the note (if there is) to the textbox in the pop form
Me yourTextBoxNameInPopUpForm = strReturn
end sub

Thanks for your help. Much appreciated

 

[arnelgp]

be warned that when you are editing a record on the first form and invoked the second form and save, microsoft will complain that the record has changed, since you are editing same record on different form.

(using double-click)
iff the first form is a new record your adding, invoking the second form will not work since, it will not find the pk because the record has not yet been created.