Current Status

[Lou P]

I have a form ("frmHW" record source = tblHW) which contains a sub-form ("subHWEvents" record source = qryHWEvents from tblEvents) in a tab control (tabHWInfo).
The "HWID" Primary Key from the tblHW links the subHWEvents HWID Foreign Key.
In the "subHWEvents" sub-form there is a Yes/No Control called "CurrentStatus", which I use to identify the current status of each piece of hardware in the table for queries or reports, (only 1 Event should reflect "Current Status" for each hardware record though there may be any number of events per hardware record). The event date is not an option as there may be events that do not change the current status, and/or multiple events with the same event date.
What I would like to do is have any HWEvent that has the "CurrentStatus" selected, to be automatically toggled to "NO" when the user selects another HWEvent as the "CurrentStatus" for that particular hardware record.
A fallback would be alert the user when they select a "HWEvent" as the "CurrentStatus" if there is already an existing "HWEvent" with the "Current Status" control selected for the current hardware record.
Any ideas on solving this issue would be greatly appreciated. Thanks!

 

[vbaInet]

Perform a search on the forum. It's been covered many times.

 

[Lou P]

I looked on the forum and other sites, but was unable to find anything that relates to what I'm trying to do...one problem may be that I'm not sure exactly what I'm looking for in Access/VBA "terminology". I find stuff related to combo boxes & text fields, alerts etc. and changing values but cannot find anything on Yes/No controls to accomplish what I'm trying to do. Could you please point me to one or more threads you're speaking of? Thanks!

 

[vbaInet]

Hmmm... ok your case is slightly different after reading through your post again.

What you need to do is to run an UPDATE query setting all the events records of the Yes/No field related to that hardware to No WHERE the hardware ID = current hardware ID AND the Id from parent = Current Parent ID AND the Event ID is Not = Current Event ID.

Then requery the subform.

That will only happen if a YES was selected and it will go in the AFTER UPDATE event of the checkbox control.

 

[Lou P]

That makes it perfectly clear...I'll try what I think you said 8>) Thanks!

 

[vbaInet]

Let us know how you get on.

 

[Lou P]

vba/Inet;

Is this what you meant?

UPDATE tblHW INNER JOIN tblEvents ON tblHW.HWID=tblEvents.HWID SET tblEvents.CurrentStatus = No
WHERE ((([tblHW].[HWID])=HWID()) AND (Not (tblEvents.EventID)=EventID()));

Run via Macro?

I was successful in changing the current status to no for all the records... while attempting to piece this together.

 

[Lou P]

I used the OBE grid to create the SQL and then entered it into the After Update for the Current_Status.

The SQL was OK with the QBE but not the Private Sub

Private Sub Current_Status_AfterUpdate()
UPDATE tblHWID
INNER JOIN tblEvents ON [tblHW].[HWID] = [tblEvents].[HWID]
SET tblEvents.CurrentStatus = No
WHERE ((([tblHW].[HWID]) = HWID()) And (Not (tblEvents.EVENTID) = EVENTID()))
Forms![frmHW]![ctlHWEvents].Form.Requery
End Sub

I get a compile error: Sub or Function not defined though I follwed an example I found...

about ready for plan B...Thanks!

 

[Lou P]

Here's as far as I can take it...

Private Sub Current_Status_AfterUpdate()
Dim SQL As String
SQL = "UPDATE tblHW " & _
"INNER JOIN tblEvents ON [tblHW].[HWID] = [tblEvents].[HWID] " & _
"SET tblEvents.CurrentStatus = No " & _
"WHERE ((([tblHW].[HWID]) = HWID()) AND (Not (tblEvents.EVENTID) = EVENTID()))"
DoCmd.SetWarnings False
DoCmd.RunSQL SQL
DoCmd.SetWarnings True
Forms![frmHW]![ctlHWEvents].Form.Requery
End Sub

And I'm not sre what's killing it...

 

[SOS]

I'm guessing that the line here is the problem:

"WHERE ((([tblHW].[HWID]) = HWID()) AND (Not (tblEvents.EVENTID) = EVENTID()))"

Do you really have a PROCEDURE (function or sub) named EVENTID or HWID? You have brackets indicating such. If you are just referring to EventID then you would need:

"WHERE ([tblHW].[HWID] = HWID) AND (Not tblEvents.EVENTID = EVENTID"

I removed some of the brackets just to have it be a bit more easily read.

 

[vbaInet]

I'm not sure how your subform is setup but I think you also need the Link Master/Child ID as well. That is the Parent ID as previously mentioned. If the HWID is not duplicated then you wouldn't need not worry about it.

 

[Lou P]

The Form/sub-form is set up as:

Hardware Form = frmHW
Record Source = tblHW
Caption = DSEI HARDWARE

The Tab Control = tabHWInfo (with 5 tabs)
Tab 0 Name = tabEvents
Tab 1 Name = tabHWRemarks
Tab 2 Name = tabHWPOC
Tab 3 Name = tabHWPurchaseInfo
Tab 4 Name = tabHWImage

The Event sub-form = subHWEvents
Record SOurce = qryHWEvents
Caption = HW EVENTS

It is in the tabcontrol(?)
Name = ctlHWEvents
Source Object = subHWEvents
Link Master Fields = HWID
Link Child Field = HWID

The "CUrrentStatus" field is on the subHWEvents

For each HW record there may be multiple events but only one should be selected (Yes/-1) as the "CurrentStatus"

When the user selects an Event as the current one, I wanted any other event that may be selected for a particular HW record to be be deselected (No/0).
If no other event is selected, then nothing happens but the update to the current Event record.

I'm sorry if I'm not giving you enough information please advise...THANKS!

 

[vbaInet]

You were on track, so here's the amended code:

docmd.setwarnings false
docmd.runsql "UPDATE tblHW INNER JOIN tblEvents ON tblHW.HWID=tblEvents.HWID " & _
             "SET tblEvents.CurrentStatus = No " & _
             "WHERE tblHW.[HWID] = [Forms]![frmHW]![subHWEvents].[Form]![HWID] AND tblEvents.EventID <> [Forms]![frmHW]![subHWEvents].[Form]![EventID];"
docmd.setwarnings true

Forms![frmHW]![ctlHWEvents].Form.Requery

I think you will be calling this code from the subform so for the requery just use Me.Requery

 

[Lou P]

EUREKA!

I had to change it to the subHWEvents to ctlHWEvents, but it works just like downtown! You were correct on the Me.Requery

docmd.setwarnings false
docmd.runsql "UPDATE tblHW INNER JOIN tblEvents ON tblHW.HWID=tblEvents.HWID " & _
"SET tblEvents.CurrentStatus = No " & _
"WHERE tblHW.[HWID] = [Forms]![frmHW]![ctlHWEvents].[Form]![HWID] AND tblEvents.EventID <> [Forms]![frmHW]![ctlHWEvents].[Form]![EventID];"
docmd.setwarnings true

Me.Requery

Thanks so much!!

 

[vbaInet]

Glad we could help