Open Form 2 from form 1, viewing a specific record. (1 Viewer)

[Woof]

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 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

'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

'open the Analysis form, telling it to use most recent record. "A"
DoCmd.OpenForm "frmAnalysis", acNormal, , , acFormEdit, , "A"

Form 2 on_open

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.

 

[NickHa]

This last bit is killing me, I can't get the GoToRecord bit to work.

Where is the definition for strPassedArgument and how do you get the Open Arguments into it? It may be that your're missing an essential part of the interface here?
Assuming you have identified the record you want in Form 1, the call to open form 2 might look like this:

DoCmd.OpenForm "frmAnalysis", acNormal, , , acFormEdit, acWindowNormal, "A" & "|" & 1234

where 1234 is the desired record number and "|" is a separator between the code and record number.
In the Form-Open event of form 2:

Rem check if an open argument is present
If Me.OpenArgs = vbNullString Then Exit Sub
Dim strParams() As String
Rem split the argument on the separator character
strParams = Split(Me.OpenArgs, "|")
Select Case strParams(0) ' this is the action code
Case "A"
    DoCmd.GoToRecord acDataForm, Me.Name, acGoTo, Val(strParam(1))  ' record number is in strParam(1)
Case "B"
    Rem whatever goes here for case "B"
Case "C"
    Rem whatever goes here for case "C"
Case "D"
    Rem whatever goes here for case "D"
Case Else
End Select

 

[Woof]

Hi and thanks for responding.

Where is the definition for strPassedArgument and how do you get the Open Arguments into it? It may be that your're missing an essential part of the interface here?

I've edited my original post to include that bit. It's not quite as complex as your version but it passes the message along.

Assuming you have identified the record you want in Form 1,

I have identified the record by using DMax on the 'Key' column. The problem is that Dmax returns the key number, which is not the record number. I can use the code with the | bar but I still need to find the record number to place in it.

It needs an expression to turn the Key number into a record number and here is where I'm flummoxed!

I originally created this problem in my code at 'Case A' but it still stands, this time I need to find the record number just before the code that calls form 1. I bet it's simple.

 

[NickHa]

What is the record source of the form you are opening? The offset is relative to that.
Have you tried Me.RecordsetClone to find the record you want when you open the form?
What about filtering the form to select the specific record - is that an option? So rather than using DoCmd.GoToRecord, just set the filter string an me.FilterOn=True.

 

[Woof]

What is the record source of the form you are opening? The offset is relative to that.
Have you tried Me.RecordsetClone to find the record you want when you open the form?
What about filtering the form to select the specific record - is that an option? So rather than using DoCmd.GoToRecord, just set the filter string an me.FilterOn=True.

Hi NickHa.

I'm a self taught newbie, and I have never heard of these ideas! Well, I have heard of recordsets and enquired about them in the past but they're still full of magic smoke to me!

The table with the information on it is a linked table called "dbo_ExtendedIncidentDetails". It's on a server a couple of miles away but thanks to ODBC I can see it as if it were local. I have bound the form to it and the form displays info from this table and none other.

I shall, however, persevere. I'll look into these ideas more, thanks.

 

[NickHa]

OK, so what you need is something like this:
In the Form Record Source, enter a query on your table "SELECT * FROM dbo_ExtendedIncidentDetails;" - you can do this in design mode or at run-time.
At run-time, in the Form_Load event code, retrieve the record key as you have done and add a filter string to the form's Filter "<field name>=" & calculated key>" (there's no ";" at the end of this string). Finally, set the FilterOn=True.
HTH!

 

[Woof]

Good morning Nick.

OK, so what you need is something like this:
In the Form Record Source,

OK, newbie ignorance here. Form Record Source? Thats where I currently have the form bound, right? I think I see.... but do you mean the calling or called form?

enter a query on your table "SELECT * FROM dbo_ExtendedIncidentDetails;" - you can do this in design mode or at run-time.

So this is where I sort it by 'Key' too? I shall have to look up the SQL for that.

At run-time, in the Form_Load event code, retrieve the record key as you have done and add a filter string to the form's Filter "<field name>=" & calculated key>" (there's no ";" at the end of this string). Finally, set the FilterOn=True.
HTH!

When I've understood the calculated key and filter mechanism I'm sure it will work. Thanks for yopur help. I'll google those terms during the day today.

 

[John Big Booty]

Just a very quick observation (without reading right through the whole thread ) You say;

Q1: This last bit is killing me, I can't get the [B]GoToRecord[/B] 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?

Are you sure you want GoToRecord Method and not the FindRecord Method?

 

[NickHa]

Good morning.

Form Record Source? Thats where I currently have the form bound, right? I think I see.... but do you mean the calling or called form?

Yes, in form design view, Property Sheet Data tab - it's the 'Record Source' field. You would do this for the called form.

So this is where I sort it by 'Key' too?

Correct. Just add ORDER BY <field name> at the end of the SQL string. If you want to look at query options, you may find this link helpful http://www.w3schools.com/sql/default.asp

When I've understood the calculated key and filter mechanism I'm sure it will work.

It certainly will! You may nead to tweak things to fit your situation, but the basis is there. BTW, the properties below the 'Record Source' in form design are the ones of interest. 'Filter' is a string which looks like a WHERE clause and you can set that programmatically by Me.Filter="<string>", probably in the Form_Load event in our case. You also set Me.FilterOn=True to make the filter active.

Thanks for yopur help

You're welcome.