Hi there
I have the following problem, and I was hoping that somebody might be able to show me where I'm going wrong.
I have a form - Form1, say - that takes its data from three tables: facs, which is a list of health facilities, and two tables which just contain data. I have a second form - Form2 - which lists the health facilities, and for each health facility there is a command button which opens up Form1 for the relevant facility.
I have code that checks whether a record for the selected facility exists in the two tables. If so, clicking the button opens Form1 filtered on the selected facility, as expected. If not, clicking the button should open the form and present a blank record; however the form is too blank - none of the controls are visible!
Here is the code for the command button - nothing too controversial!
I have tried this for when Form1 is based only on the table facs and one data table, and it has worked fine. I'm feeling very bamboozled!
This is the SQL on which Form1 is based - I think the problem must be here, but I'm not sure.
Any advice would be most gratefully received, and I'd just like to say how much I've learnt from just browsing the forum here, so thank you very much to everyone who contributes.
I have the following problem, and I was hoping that somebody might be able to show me where I'm going wrong.
I have a form - Form1, say - that takes its data from three tables: facs, which is a list of health facilities, and two tables which just contain data. I have a second form - Form2 - which lists the health facilities, and for each health facility there is a command button which opens up Form1 for the relevant facility.
I have code that checks whether a record for the selected facility exists in the two tables. If so, clicking the button opens Form1 filtered on the selected facility, as expected. If not, clicking the button should open the form and present a blank record; however the form is too blank - none of the controls are visible!
Here is the code for the command button - nothing too controversial!
Code:
If Me.coa = 1 Then
DoCmd.OpenForm "Form1", , , "[facs.HID] = '" & Me.hid & "'", acFormEdit
ElseIf Me.coa = 0 Then
DoCmd.OpenForm "Form1", , , "[facs.HID] = '" & Me.hid & "'", acFormAdd
End If
This is the SQL on which Form1 is based - I think the problem must be here, but I'm not sure.
Code:
SELECT facs.hid, facs.fac, ab.hid, ab.a, ab.b, cd.hid, cd.c, cd.d FROM (ab INNER JOIN cd ON ab.hid = cd.hid) INNER JOIN facs ON (cd.hid = facs.hid) AND (ab.hid = facs.hid);