Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

VBA help, please "No current Record"

Discussion in 'Information Technology' started by Colin Foster, Jul 28, 2006.

  1. Colin Foster

    Colin Foster
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Colin Foster" <colin@NOusSPA4b.co.Muk> wrote in message
    news:44ae746c$0$69389$ed2619ec@ptn-nntp-reader01.plus.net...
    > 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
    >
     
  4. Colin Foster

    Colin Foster
    Expand Collapse
    Guest

    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" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:uftrsjdoGHA.524@TK2MSFTNGP05.phx.gbl...
    > 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
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Colin Foster" <colin@NOusSPA4b.co.Muk> wrote in message
    > news:44ae746c$0$69389$ed2619ec@ptn-nntp-reader01.plus.net...
    >> 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
    >>

    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Colin Foster" <colin@NOusSPA4b.co.Muk> wrote in message
    news:44ae8bc4$0$3500$ed2619ec@ptn-nntp-reader01.plus.net...
    >
    > 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
     
  6. Colin Foster

    Colin Foster
    Expand Collapse
    Guest

    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


    "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
    news:uqMeoVeoGHA.4996@TK2MSFTNGP05.phx.gbl...
    > 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.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Colin Foster" <colin@NOusSPA4b.co.Muk> wrote in message
    > news:44ae8bc4$0$3500$ed2619ec@ptn-nntp-reader01.plus.net...
    >>
    >> 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

    >
    >
     

Share This Page