Hi all.
Excuse the length of this post as I'm trying to include all the necessary detail.
Form 1 (a control panel / switchboard) has a series of command buttons which I wish to use to open Form 2 (where work is done) and take the user to a record which has a specific marker. Different buttons for different markers. The records containing this marker will vary depending on the work previously done. I'm trying to make this as simple as possible so no-one has to think about anything, just press a button!
The marker is in an integer field in the record called "Status". By default it is set to 1 (meaning no work done), so all new records have this state. When certain actions happen the status can change to 2, 3 or 4.
When one of these buttons on form 1 is pressed, the user should have the most recent record which has the status field set to 1 displayed in form2. Once I have this working I can vary it for the other buttons.
I am currently thinking of using OpenArgs between the two forms. I can change this of course, depending on your advice.
So, I think the system needs to know:
How many records. (Currently 29434).
Which record does the button being pressed wish to show?
Which record is the most recent with Status = 1
How the table is sorted. (By key ascending)
An (abbrevieated) table looks like this:
Key 46 would be a record added since work was last carried out. 1 to 35 would be those not yet worked on. Note the key numbers will have gaps where errors or deletions have occured so they won't match the sorted record numbers.
My idea is:
Form 1 on_current
Form 1 on_buttonclick
Form 2 on_open
Q1: This last bit is killing me, I can't get the GoToRecord bit to work. It needs an expression to turn the Key number into a record number and here is where I'm flummoxed! Can anyone advise please?
Q2: ALSO, where would you put the code (and what code) to sort the table as required? Form 1 on_open / load?
Q3: Am I making this too long winded? Is there a better way?
I'm off to do some chainsawing this afternoon so I'll (hopefully ) look back in here this evening. UK time. Thank you in advance.
Mike.
Excuse the length of this post as I'm trying to include all the necessary detail.
Form 1 (a control panel / switchboard) has a series of command buttons which I wish to use to open Form 2 (where work is done) and take the user to a record which has a specific marker. Different buttons for different markers. The records containing this marker will vary depending on the work previously done. I'm trying to make this as simple as possible so no-one has to think about anything, just press a button!
The marker is in an integer field in the record called "Status". By default it is set to 1 (meaning no work done), so all new records have this state. When certain actions happen the status can change to 2, 3 or 4.
When one of these buttons on form 1 is pressed, the user should have the most recent record which has the status field set to 1 displayed in form2. Once I have this working I can vary it for the other buttons.
I am currently thinking of using OpenArgs between the two forms. I can change this of course, depending on your advice.
So, I think the system needs to know:
How many records. (Currently 29434).
Which record does the button being pressed wish to show?
Which record is the most recent with Status = 1
How the table is sorted. (By key ascending)
An (abbrevieated) table looks like this:
Key status data
1 1 data
2 1 data
3 1 data
5 1 data
23 1 data
35 1 data
36 2 data
37 4 data
38 2 data
39 2 data
40 3 data
41 2 data
43 2 data
46 1 data
Key 46 would be a record added since work was last carried out. 1 to 35 would be those not yet worked on. Note the key numbers will have gaps where errors or deletions have occured so they won't match the sorted record numbers.
My idea is:
Form 1 on_current
Code:
'Count how many records there are and put that value into an accessible box
Forms!frmControlPanel!boxNumberOfRecords.Value = DCount("*", "dbo_ExtendedIncidentDetails")
'Find the highest record with status = 1
Forms!frmControlPanel!boxLatestUnanalysed.Value = DMax("MI_Prinx", "dbo_ExtendedIncidentDetails", "status = 1")
Form 1 on_buttonclick
Code:
'open the Analysis form, telling it to use most recent record. "A"
DoCmd.OpenForm "frmAnalysis", acNormal, , , acFormEdit, , "A"
Form 2 on_open
Code:
Private Sub Form_Open(Cancel As Integer)
'MsgBox "Calling Form_Open()"
'Retrieve arguments sent by Control Panel
Dim strPassedArgument As String
strPassedArgument = Forms!frmAnalysis.OpenArgs
If Len(strPassedArgument) > 0 Then
If strPassedArgument = "A" Then
DoCmd.GoToRecord acDataForm, "dbo_ExtendedIncidentDetails", acGoTo, DoCmd.FindRecord Forms!frmControlPanel!boxLatestUnanalysed.Value, , No, All, , No, Yes
End If
If strPassedArgument = "B" Then
End If
If strPassedArgument = "C" Then
End If
If strPassedArgument = "D" Then
End If
End If
Q1: This last bit is killing me, I can't get the GoToRecord bit to work. It needs an expression to turn the Key number into a record number and here is where I'm flummoxed! Can anyone advise please?
Q2: ALSO, where would you put the code (and what code) to sort the table as required? Form 1 on_open / load?
Q3: Am I making this too long winded? Is there a better way?
I'm off to do some chainsawing this afternoon so I'll (hopefully ) look back in here this evening. UK time. Thank you in advance.
Mike.
Last edited: