Here's an intermediate technique idea for you.
Create a form based on a table or query.
Put the form into "Continuous Form" mode (this is the one that doesn't look like a 'table') and shows many records, just one at a time.
When in design mode, all you'll see are the given field names which you can move around however you wish. Add a button to that form with a 'click event'.
Now, here's the trick. When you open the form in regular mode (the nice one you see when you're using the form), each record will show up with it's own button. The act of clicking a button does two things. First, the record next to the button is "selected" AND the onclick event of the button is executed.
In the onclick event put code that references values in your table/query.
You can reference fields within the record using syntax such as:
Me.fieldname1
Now, back to Nories suggestion. Norie is suggesting you find some way to unique identify each record in your table. This is critically important because what it does is allow you to dynamically generate a query with a Parameter passed to it that will only affect your one selected record.
Assuming the field 'fieldname1' has that value, you can do something like this.
Code:
If Len(Me.fieldname1) > 0 Then
Call BeginTransaction(Me.fieldname1)
End If
This does two things. First, it has a test to ensure something is in the identifying field. Next, it sends that information over to a function called 'BeginTransaction' which needs information passed to it (the value of fieldname1)
Code:
Sub BeginTransaction(ByRef strParameter As String)
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As QueryDef
Dim strSQL, strQryName As String
Set dbs = CurrentDb()
strSQL = "SELECT * FROM tblName "
strSQL = strSQL & " WHERE afieldname='" & strParameter & "'"
strQryName = "YourQuery"
Set qdf = dbs.CreateQueryDef(strQryName, strSQL)
End Sub
There's a few things to change here, this is just a sample that will create a select query for you. It will, of course, generate errors if you attempt to create a 2nd copy until you delete the first.
Now, here's how you do this for your particular problem.
Remove the qdf references above (they're not needed for this)
Below, .fields(0).value is equivalent to rs.fields(0).value and extracts the value in the very first field in the recordset (it starts at 0 by default)
I'll let you puzzle out what the rest of this does. But, as a hint, it allows you to control layout of the print (portrait vs landscape -- necessary since there is a documented bug where Access resets to Portrait when you close the database) and to view vs Print the report. As a note, make sure you have references set to the DAO 3.x object library AND whenever you add type declarations to a code module (2nd code segment below) you must add them to the top of the code module.
Code:
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
myOrient = 1 ' Landscape format 0 is Portrait
testvalue = .fields(0).value
Call OpenReport(testvalue, myOrient)
End with
Code:
Type str_DEVMODE
RGB As String * 94
End Type
Type type_DEVMODE
strDeviceName As String * 16
intSpecVersion As Integer
intDriverVersion As Integer
intSize As Integer
intDriverExtra As Integer
lngFields As Long
intOrientation As Integer
intPaperSize As Integer
intPaperLength As Integer
intPaperWidth As Integer
intScale As Integer
intCopies As Integer
intDefaultSource As Integer
intPrintQuality As Integer
intColor As Integer
intDuplex As Integer
intResolution As Integer
intTTOption As Integer
intCollate As Integer
strFormName As String * 16
lngPad As Long
lngBits As Long
lngPW As Long
lngPH As Long
lngDFI As Long
lngDFr As Long
End Type
Public Function ConvertToLandscape(ByVal strName As Variant, myOrient As Variant)
Dim rpt As Report
Dim strDevModeExtra As String
Dim DevString As str_DEVMODE
Dim DM As type_DEVMODE
'strName = "repTimeDelayedTest"
DoCmd.OpenReport strName, acDesign 'Opens report in Design view.
Set rpt = Reports(strName)
If Not IsNull(rpt.PrtDevMode) Then
strDevModeExtra = rpt.PrtDevMode
DevString.RGB = strDevModeExtra
LSet DM = DevString
DM.lngFields = DM.lngFields Or DM.intOrientation 'Initialize fields.
DM.intOrientation = myOrient 'Landscape
LSet DevString = DM 'Update property.
Mid(strDevModeExtra, 1, 94) = DevString.RGB
rpt.PrtDevMode = strDevModeExtra
DoCmd.Save acReport, strName
DoCmd.Close acReport, strName
End If
End Function
Public Function OpenReport(ByVal repName As Variant, ByVal myOrient As Variant, _
Optional bolPrintOnly As Boolean)
Dim viewPref As VbMsgBoxResult
If Not bolPrintOnly Then
viewPref = MsgBox("Select Yes if you wish to print.", vbYesNo + vbDefaultButton2, "View or Print")
End If
Call ConvertToLandscape(repName, myOrient)
If bolPrintOnly Then viewPref = 6
Select Case viewPref
Case 6 'Yes
DoCmd.OpenReport repName, acNormal, "", ""
DoCmd.Close acReport, repName
Case Else 'No
DoCmd.OpenReport repName, acPreview, "", ""
End Select
End Function
Ps - Maybe I lied. This might be advanced technique, not intermediate.
Mike
