VBA help, please "No current Record"

[Colin Foster]

Hi There,
I have a database with the following code attached to the OnClick event of a
button. The idea is when a Job has been created from a quotation, then this
will report all of the relevant details of the job.

Private Sub PrintJobSheet_Click()
Dim strRecordSource As String, strFormFilter As String
Dim stDocName As String
Dim strfilter As String

On Error GoTo Err_PrintJobSheet_Click

Me.Dirty = False
DoEvents

stDocName = "RptJobSheet"
strfilter = "jobno = " & Me.JobNo.Value

' Store the current recordsource and filter strings
strRecordSource = Me.RecordSource
strFormFilter = Me.Filter

' Freeze screen so that the data do not appear to change
DoCmd.Echo False

' Remove the current recordsource so that the report can use it
Me.RecordSource = ""

DoCmd.OpenReport stDocName, acPreview, , strfilter

Exit_PrintJobSheet_Click:

' Restore the recordsource to what it was
Me.RecordSource = strRecordSource
' Restore the filter to what it was
Me.Filter = strFormFilter

' Turn filter back on
Me.FilterOn = True

' Return to the record that was active when the button was clicked
Me.RecordsetClone.FindFirst strfilter
Me.Bookmark = Me.RecordsetClone.Bookmark

' Unfreeze the screen
DoCmd.Echo True
Exit Sub

The code was originally provided to me from one of the Access Newsgroups a
couple of years ago & seemed to work perfectly. However, now that I've
started to use this button more, I'm finding that when I click the button I
get an error dialog of "No Current Record". When I click the OK button on
this dialog, I then get another error dialog of "No Current Record" that
continues to loop in the same way until I get fed up & press CTRL+Break!

Going into the VBA coding, when I'm in DeBug mode, I've also noticed that
when I hover my mouse over the line of coding line...

Me.Bookmark = Me.RecordsetClone.Bookmark,

then I have a (not sure of technical term, but it's like ControlTip Text)
of Me.Bookmark = <No Current Record.> which is obviously what's causing the
issue.

Interestingly, some jobs print out ok, it's only when I'm creating a new job
that the issue arises. I have a button on my form to save the job, & that
appears to work. I've even gone back to the version that I had before
putting through these mods & realised that that isn't working, either (so
I've satisfied myself that it's not something that I've done on this Mod!

I'm using Access XP (in Access 2000 mode) to write the database (but also
have Access 2000 & 2003 installed) & th epc that's using the database is on
Access 2000. The database is also slpit between front & back ends.

Hope there's enough there for you guys to work on.

Regards
Colin Foster

 

[Allen Browne]

Colin, 90% of the stuff in that code is unnecessary.

It is also faulty at several levels:
a) It stores the filter without testing FilterOn.

b) It applies the filter that may be no more than an artifact from a
previous time (which would explain the no current record);

c) It assigns strFilter without testing if JobNo is null (which it would be
at a new record.)

d) It uses the bookmark of the form, without testing NewRecord (which it
could be after a spurious filter.)

and so on.

Try this:
Private Sub PrintJobSheet_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "jobno = " & Me.JobNo
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub

Explanation of how it works in this article:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

 

[Colin Foster]

Allen,

First of all thanks not only for the code (which works!), but also for the
explanations... as I'm sure that you've guessed, I only "dabble" in VBA...
the way some of you guys get to know the intricacies blows my mind!!

I now realise that I have another issue which may also be due to
unnecessary/wrong coding, so I'd like to bounce that one off you, too,
because I suspect that some of the issues are the same as you have just
resolved...

The basic process is that a quote is created then, if successful a job is
created from that quote. This is done via a button on the form frmQuotes &
attached to the button's OnClick event is coding to open up the form
frmJobSheet. Where there are existing jobs associated with the quote, then
clicking this button should bring up the job associated with the quote; if
there isn't one, then it should bring up a new form (JobNumber is
Autonumber) but already populated with much of the information from the
quote.

What I'm finding is that "old" jobs are appearing in the "correct" way (i.e.
if quote 123 has job 456, then clicking on the "JobSheet" button whilst
looking at quote 123 will bring up th ejob sheet for job 456), but newly
created jobs are not doing this.

First of all, here's the code behind the OnClick event of the button...

Private Sub OpenJobSheet_Click()
On Error GoTo Err_OpenJobSheet_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmJobSheet"

stLinkCriteria = "[QuoteID]=" & Me![QuoteID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenJobSheet_Click:
Exit Sub

Err_OpenJobSheet_Click:
MsgBox Err.Description
Resume Exit_OpenJobSheet_Click

End Sub

As you can see, there is a link using the field QuoteID

Interestingly, if I look at the properties of the form frmJobSheet, then
there is a filter applied of: [QuoteID]=1243 (if I am looking at , e.g.,
Quote 17 when I go into the properties of frmJobSheet, then "17" replaces
"1243" in the above filter.

Don't know how much more info you need?

Regards
Colin

 

[Allen Browne]

It makes sense to have a command button that converts a quote into a job
when the user approves the quote.

The specifics will depend on the data structure, of course.
You probably have tables such as:
- Quote (one record per quote);
- QuoteDetail (one record for each line item in a quote);
- Job (one record for each job);
- JobDetail (one record for each line item in a job).

Chances are that not all line items in a quote become line items of the job.
Client may choose to accept only some of the quote, so the quote line items
might have quoted some items in different ways (e.g. different quantities.)

But if the button is to create a new job (probably with a reference to the
source quote), and all the line items (which can then be edited/delted if
necessary), then the command button would need to:
a) OpenRecordset on the Job table, Addnew, and Update, and so retrieve the
primary key value of the new job;
b) Execute an Append query statement that selects all the line items from
QuoteDetail, and adds them to JobDetail with foreign key set to the new
JobID.

You will need to have some skills in VBA and SQL to write that code. It will
be something like the code in this article:
http://allenbrowne.com/ser-57.html
But you will OpenRecordset on the Job table instead of using the
RecordsetClone of the form.

(I did not really follow how your form is currently trying to do this. There
must be other code elsewhere that is part of that process also.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

First of all thanks not only for the code (which works!), but also for the
explanations... as I'm sure that you've guessed, I only "dabble" in VBA...
the way some of you guys get to know the intricacies blows my mind!!

I now realise that I have another issue which may also be due to
unnecessary/wrong coding, so I'd like to bounce that one off you, too,
because I suspect that some of the issues are the same as you have just
resolved...

The basic process is that a quote is created then, if successful a job is
created from that quote. This is done via a button on the form frmQuotes &
attached to the button's OnClick event is coding to open up the form
frmJobSheet. Where there are existing jobs associated with the quote, then
clicking this button should bring up the job associated with the quote; if
there isn't one, then it should bring up a new form (JobNumber is
Autonumber) but already populated with much of the information from the
quote.

What I'm finding is that "old" jobs are appearing in the "correct" way
(i.e. if quote 123 has job 456, then clicking on the "JobSheet" button
whilst looking at quote 123 will bring up th ejob sheet for job 456), but
newly created jobs are not doing this.

First of all, here's the code behind the OnClick event of the button...

Private Sub OpenJobSheet_Click()
On Error GoTo Err_OpenJobSheet_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmJobSheet"

stLinkCriteria = "[QuoteID]=" & Me![QuoteID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenJobSheet_Click:
Exit Sub

Err_OpenJobSheet_Click:
MsgBox Err.Description
Resume Exit_OpenJobSheet_Click

End Sub

As you can see, there is a link using the field QuoteID

Interestingly, if I look at the properties of the form frmJobSheet, then
there is a filter applied of: [QuoteID]=1243 (if I am looking at , e.g.,
Quote 17 when I go into the properties of frmJobSheet, then "17" replaces
"1243" in the above filter.

Don't know how much more info you need?

Regards
Colin

 

[Colin Foster]

Hi Allen,
I appear to have managed to resolve this issue... my form wasn't correctly
updating the Query/Table that it needed to! I've now fixed this link & it
appears to work...

Thanks for your help

Regards
Colin

It makes sense to have a command button that converts a quote into a job
when the user approves the quote.

The specifics will depend on the data structure, of course.
You probably have tables such as:
- Quote (one record per quote);
- QuoteDetail (one record for each line item in a quote);
- Job (one record for each job);
- JobDetail (one record for each line item in a job).

Chances are that not all line items in a quote become line items of the
job. Client may choose to accept only some of the quote, so the quote line
items might have quoted some items in different ways (e.g. different
quantities.)

But if the button is to create a new job (probably with a reference to the
source quote), and all the line items (which can then be edited/delted if
necessary), then the command button would need to:
a) OpenRecordset on the Job table, Addnew, and Update, and so retrieve the
primary key value of the new job;
b) Execute an Append query statement that selects all the line items from
QuoteDetail, and adds them to JobDetail with foreign key set to the new
JobID.

You will need to have some skills in VBA and SQL to write that code. It
will be something like the code in this article:
http://allenbrowne.com/ser-57.html
But you will OpenRecordset on the Job table instead of using the
RecordsetClone of the form.

(I did not really follow how your form is currently trying to do this.
There must be other code elsewhere that is part of that process also.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

First of all thanks not only for the code (which works!), but also for
the explanations... as I'm sure that you've guessed, I only "dabble" in
VBA... the way some of you guys get to know the intricacies blows my
mind!!

I now realise that I have another issue which may also be due to
unnecessary/wrong coding, so I'd like to bounce that one off you, too,
because I suspect that some of the issues are the same as you have just
resolved...

The basic process is that a quote is created then, if successful a job is
created from that quote. This is done via a button on the form frmQuotes
& attached to the button's OnClick event is coding to open up the form
frmJobSheet. Where there are existing jobs associated with the quote,
then clicking this button should bring up the job associated with the
quote; if there isn't one, then it should bring up a new form (JobNumber
is Autonumber) but already populated with much of the information from
the quote.

What I'm finding is that "old" jobs are appearing in the "correct" way
(i.e. if quote 123 has job 456, then clicking on the "JobSheet" button
whilst looking at quote 123 will bring up th ejob sheet for job 456), but
newly created jobs are not doing this.

First of all, here's the code behind the OnClick event of the button...

Private Sub OpenJobSheet_Click()
On Error GoTo Err_OpenJobSheet_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FrmJobSheet"

stLinkCriteria = "[QuoteID]=" & Me![QuoteID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenJobSheet_Click:
Exit Sub

Err_OpenJobSheet_Click:
MsgBox Err.Description
Resume Exit_OpenJobSheet_Click

End Sub

As you can see, there is a link using the field QuoteID

Interestingly, if I look at the properties of the form frmJobSheet, then
there is a filter applied of: [QuoteID]=1243 (if I am looking at , e.g.,
Quote 17 when I go into the properties of frmJobSheet, then "17" replaces
"1243" in the above filter.

Don't know how much more info you need?

Regards
Colin