General discussion

Locked

Access 2000 Finding a record number

By pc_tek ·
I am putting together an Access 2000 database application that requires a report to print a specific record after the form is filled out. I can ask querry to do it, but would like to automate this sequence. Does anyone know some visual basic code that can be used to trap the current record number of the form after it has been saved and refreshed, and then save it as a variable? Thanks in advance.

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 2000 Finding a record number

by timfox In reply to Access 2000 Finding a rec ...

Chances are that you are using a form that is bound to a recordset. (Most are, but if not, let us know as answer could be different).

Use this code within the OnClick procedure of your form's Save/OK/Close button:

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
If IsNumeric(txtRecordNum) Then
Set rst = db.OpenRecordset("SELECT RecordNum FROM tblThisTable WHERE RecordNum = " & txtRecordNum)

If rst.RecordCount = 1 Then DoCmd.OpenReport "rptThisReport", acViewPreview, , "RecordNum = " & txtRecordNum
Else
MsgBox "ERROR - Record not added to DB", vbCritical, "Notify Administrator"
End If

End If

rst.Clone
db.Close

Note
1. This code forms the basis of what you could do, but changes will need to be made because your field names, text box names and recordsets etc will be different.
2. The DoCmd.RunCommand acCmdSaveRecord may not be needed.

Hope it Helps
TimFox

Collapse -

Access 2000 Finding a record number

by pc_tek In reply to Access 2000 Finding a rec ...

Poster rated this answer

Collapse -

Access 2000 Finding a record number

by timfox In reply to Access 2000 Finding a rec ...

Additional thoughts...
Ensure that your report's FilterOn property is set to True.
The code above does not use a global variable, although doing so is another way of solving your problem. To use a global variable, you would have to ensure the report also references the global variable in either it's query or the filter property.

Assigning the RecordNum value to a global variable is done by

1. Setting up and writing a method to return the global variable
Create a module with the following contents:

Option Compare Database
Option Explicit
Public gblRecordnum As Integer

Public Function GetRecordNum() As Integer
If IsNumeric(gblRecordnum) Then
GetRecordNum = gblRecordnum
Else
GetRecordNum = -1
End If
End Function
---------------

2. Change your On_Click to this:

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
If IsNumeric(txtRecordNum) Then
Set rst = db.OpenRecordset("SELECT RecordNum FROM tblThisTable WHERE RecordNum = " & txtRecordNum)

If rst.RecordCount = 1 Then
gblRecordnum = txtRecordNum
DoCmd.OpenReport "rptThisReport", acViewPreview
Else
MsgBox "ERROR - Record not added", vbCritical, "Notify Administrator"
End If

End If

rst.Clone
db.Close
---------------

3. Place this code in the report's On_Open procedure:

If GetRecordNum > 0 Then
Me.Filter = "RecordNum = " & GetRecordNum
Else ' function returned -1
MsgBox "ERROR - GetRecordNum", vbCritical
End If
---------------

Good Luck
TimFox

Collapse -

Access 2000 Finding a record number

by pc_tek In reply to Access 2000 Finding a rec ...

Thanks Tim for your help. Ill give it a try.

Collapse -

Access 2000 Finding a record number

by pc_tek In reply to Access 2000 Finding a rec ...

This question was closed by the author

Back to Web Development Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums