Check if linked tbl has record then load form (1 Viewer)

[DaaAgent]

Hello

I have small .mdb containing these tables

tbl_ClientsMaster (Primary Key) (PID)

All linked to above by PID
tbl_ClientsMedicalDetails (PMDID)
tbl_ClientsExecutiveCards (PECID)
tbl_ClientsPreferences (PCPID)

The tables all have corresponding forms

When the frm_ ClientsMaster loads I would like access to check if any of these connected tbl has a connected record then display the appropriate form or forms

There is the possibility that the client could have a connected recorded in each tbl

Got as far as working out the Trigger in the frm_ ClientsMaster will be the On Current with a IIF statement or a Case statement.

I have used IIF statements in the past for specific fields but never to check if a record is present

Thanking you in advance for your help in this matter

stewart

Access2000

 

[RuralGuy]

I would think you would use a MainForm/SubForm to take care of this pretty much automatically.

 

[DaaAgent]

Hi Allan thanks for taking the time to reply

Due to screen size restrictions the original frm had a TabCtl & Subforms, But my colleagues just didn’t look through the tabs

I thought if i changed the other forms to popups this would force them at lest to look at them and further down the line if there was any problems it’s their fault. Also i believe i will have a cause later on to reverse the process ie if a record doesn’t exist in the connected tbl a frm would load to input a record

The code I had started will obviously works fine if there is a connected record but if their isn’t it still opens but blank

Option Compare Database

Private Sub Form_Current()

stDocName = "frm_ClientsMedicalDetails"
stLinkCriteria = "[PID]=" & Me![LinkPID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

stDocName = "frm_ClientsExecutiveCards"
stLinkCriteria = "[PID]=" & Me![LinkPID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

stDocName = " frm_ClientsPreferences "
stLinkCriteria = "[PID]=" & Me![LinkPID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub


So the bit i think I am missing is getting access to check if there is a connected record before going ahead with the OpenForm cmd

Cheers
Stewart

 

[RuralGuy]

You could use DCount() to check for > 0 records before running the OpenForm command.

 

[DaaAgent]

Ok DCount it is then

Looked at the Access help pages their was an example
“There are more than three entries in the Order Details table for which the OrderID field of the table matches the OrderID field on the Orders form. “
DCount("*", "Order Details", "[OrderID]=Forms![Orders]![OrderID]")>3

Will the code have to start with an IIF ?
DCount("*", "frm_ClientsMedicalDetails", "[PID]=Forms![frm_clientmasters]![PID]")>0

Sorry Allan I am still needing a wee push

 

[gemma-the-husky]

syntax is off

it needs

IF DCount("*", "frm_ClientsMedicalDetails", "[PID] = " & Forms![frm_clientmasters]![PID])>0 then
 ...whatever
end if

 

[RuralGuy]

Thanks for stepping in Dave.

 

[DaaAgent]

Never put together a multiple IIF before. This was fun typing this out on my Mobile !!!. Back in the office on Monday

Will this code work ?

Thanks
Stewart



Private Sub Form_Current()

IIf (DCount("*", "frm_ClientsMedicalDetails", "[pid]=Forms![frm_clientmasters]![pid]")>0 ) Then

stDocName = "frm_ClientMedicalDetails"
stLinkCriteria = "[PAXID]=" & Me![PAXID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

IIf (DCount("*", "frm_ClientMembershipsClubs", "[pid]=Forms![frm_clientmasters]![pid]")>0 ) Then

stDocName = "frm_ClientMembershipsClubs"
stLinkCriteria = "[PAXID]=" & Me![PAXID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

IIf (DCount("*", " frm_ClientPrefernces", "[pid]=Forms![frm_clientmasters]![pid]")>0 ) Then

stDocName = " frm_ClientPrefernces"
stLinkCriteria = "[PAXID]=" & Me![PAXID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

End If
End If
End If

End Sub

 

[RuralGuy]

The simple answer is NO. The Immediate IF (IIF) is not used in this situation and does not involve a THEN, and leading spaces in some of your strings will almost certainly not work. That is for starters.

 

[DaaAgent]

I was trying to use the example Dave gave me with the Then at the end.

So a “Then” is used when is a “IF” not a “IIF” ?

Also I don’t understand “leading spaces” is that not to do with Trim function

I must admit I just can’t get my head round the structure of this. I am officially lost

Stewart

 

[gemma-the-husky]

this is the problem - using this expression within the dcount

"[pid]=Forms![frm_clientmasters]![pid]"

asks Access to check for PID equal to the expression Forms![frm_clientmasters]![pid] - which is meaningless

you have to put it as

"[pid]= " & Forms![frm_clientmasters]![pid]

then it resolves the RHS into the VALUE of the form reference and becomes

"[pid]= 6") (say)

------
an iif (immediate if) is an inline statement

effectively if a, then b, else c

an if (if then else) - is a code statement, with alternative syntax

if condition then dosomething - all on one line - no alternative

if condition then
   dosomething
else
   dosomethingelse
end if

this latter version is more akin to an iif - but is nore readable in code. in queries, you can't use this structure, hence the use of iif.