Popup form - Find Record or Add New Record

[Lynn_AccessUser]

I have a main form with a continuous subform. On each record on the subform the user can check a box which if true will open up a new form.

The Subform is built on TableA and the popup form is built on TableB. The primary and foreign keys in both tables is called RegItemID.

The problem is as follows:

If ReqItemID exists in TableB then open the popup form to that record where popform.ReqItemID = forms!mainform!subform!ReqItemID

If ReqItemID does not exist in TableB then open to a new record and set popform.ReqItemID = forms!mainform!subform!ReqItemID.

Thanks!!!

 

[pono1]

Lynn,

This is rough, pseudo code -- I'll leave it to you to smooth out the edges. Search on OpenForm and DCOUNT in Help for syntax and example code.

'Create var.
	Dim intChk as integer

'Count the number of rec instances in tableb.
	IntChk = DCOUNT(RegItmIDField, TableB, FK = Forms!FormName!TextBoxRegItmID)

'Does the rec exist?
	If intChk = 0 then 'No?
		DoCmd.OpenForm "PopFrmName",,,,acformadd
	Else 'Yes?
		DoCmd.OpenForm PopFrmName,,,"RegID = " & Forms!FormName!TxtRegItmID

	End if

Regards,
Tim