Solved

Access 2007 Updating a record using DAO from Form Button

Posted on 2010-09-01
6
547 Views
Last Modified: 2012-05-10
I have been trying to write code that will use a forms' information (2 combo boxes and other text boxes) to create a label (report) on demand from the user.

I can successfully populate the print table [LblVarPrint] and then the action of printing the label is also a success (using the VarCopies).

Where I am having a problem is that after the report prints, I need to have the ("PrintComplete") field toggle from the "N" to a "Y".  (Lines 29-34 are not working)

I realize that there is a gaping hole in my understanding of moving through the record set.  So if you have pointers (websites, links to good tutorials) they are welcomed.  And also comments chiding me on bad code are not too welcomed, although much deserved!  

Points will be awarded to someone who can help me clean up the mess from my code.  And help me see where I take a wrong turn.  Apologies, I am new at using DAO.

Thanks in advance for your support and guidance.

Sincerely,
KLB
Private Sub Command14_Click()

    Dim rst As DAO.Recordset
    Dim strSQL As String

    'Open the recordset
    strSQL = "SELECT * FROM LblVarPrint WHERE False"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    'Add New Record for Print
    rst.AddNew
    rst("PartNum") = Me.PartNum
    rst("Descpt") = Me.Descpt
    rst("Sellm") = Me.LblSellm
    rst("Lbl_Dte_FullCode") = Me.Cmb_RT
    rst("CountrySpelling") = Me.Cmb_CS
    rst("PrintComplete") = "N"
    rst.Update
    rst.Close
    
    'Passing QtyToPrint to report
    Dim VARCopies As Integer
    VARCopies = Me.QtyPrint
    DoCmd.OpenReport "LblSmVar", acViewPreview
    DoCmd.PrintOut acPrintAll, , , , VARCopies, True
    
    'Close Report
    DoCmd.Close acReport, "LblSmVar", acSaveNo
           
    'Go to last record and update Printed from "N" to "Y"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    rst.Edit
    rst.MoveLast
    rst("PrintComplete") = "Y"
    rst.Update
      
    rst.Close
    Set rst = Nothing
    
    'Close Form
    DoCmd.Close acForm, "LblVarPrnt", acSaveNo
        
    
End Sub

Open in new window

0
Comment
Question by:CPKGDevTeam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 4

Expert Comment

by:tabish
ID: 33584127
Try changing the following segmanrt of code
     Set rst = CurrentDb.OpenRecordset(strSQL)
    rst.Edit
    rst.MoveLast
to

    Set rst = CurrentDb.OpenRecordset(strSQL)
    rst.MoveLast
    rst.Edit

I believe changes to the field are lost if record position changes in edit-update. I hope moving to the last first and then editing the field will help.
0
 
LVL 11

Accepted Solution

by:
LambertHeenan earned 100 total points
ID: 33586616
It is important to realize that in SQL databases there is no such thing as the "last record" in a table. Or more accurately, the order or records in a table is undefined and so you cannot make any assumptions about that order.
One way to solve your issue is to keep track of the primary key value of the newly created record. One would hope that your table LBLVARPrint has an AutoNumber field in it, and for the sake of this discussion I'll assume it is called "ID". In which case you could modily your code this way...
Dim NewRecordID as Long
    rst.AddNew
    rst("PartNum") = Me.PartNum
        ' at this point data has been added to the new record, and so
        'Access will imediately assign an Autonumber value to that record.

    NewRecordID= rst!ID ' store the value of the ID field in a variable
    ' and then the rest of the code to add the new record data.
Note that I use a different syntax to access the ID field. There is no special reason for this. I just did it to show you that there is another  way to reference fields in a recordset without having to use all those parentheses and quotes. rst!ID is equivalent to rst("ID")
Now that you have the record's ID value stored away, when you come to the update part of you code you can change it to...
   
    Set rst = CurrentDb.OpenRecordset(strSQL)
    rst.FindFirst "ID=" & NewRecordID
    If Not rst.NoMatch Then
        rst.Edit
        rst("PrintComplete") = "Y"
        rst.Update
    End If  
    rst.Close
HTH
Lambert
0
 

Author Comment

by:CPKGDevTeam
ID: 33588119
tabish:  Thanks for your option - but it did not work for me.

LambertHeenan:

Your code looks like it is taking me on the right path, but I am now getting an error 94 "Invalid use of Null".

Does this mean that I am not getting an ID until the actual rst.Update?  I looked at my back end table (SQL 2000) and found that the primary key ID field (in my database is 'LblPrntID') has a data type of numeric.  I have attached a snapshot of the table and its fields' properties in the hope that you might be able to see if my problem starts there.

I have also added a snapshot of the code and where the debug is getting invoked.  

I can delete this table and recreate if I need to make changes to the Primary Key settings etc.  Please advise and thank you for your very easy to understand directions (as well as the different examples of how to store the variable for ID - nice touch).

I would appreciate anymore help you can give,
Thank you,

KLB
Table.jpg
debug.jpg
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:LambertHeenan
ID: 33588536
KLB
I believe you are correct. Becuase the primary key field is just Numeric it is not automatically assigned a value when a new record is created. In SQL server I believe the correct data type would be "Identity".
So I suggest that you change it's type accordingly. It should then be assigned a value automatically assoon as any ata is added to any other fields.
Lambert
0
 

Author Closing Comment

by:CPKGDevTeam
ID: 33589917
Can't thank you enough.  Great help and code that I can really use several times.

0
 

Author Comment

by:CPKGDevTeam
ID: 33589951
Lambert: (and others who may have the same issues)

I had to make the table local to figure out the error, as I think the problem has to do with the backend SQL and DAO with "dbSeeChanges" option.  The errors were hard to get through - so I simplified the table by making it local and it worked immediately.  Thank you!

Sincerely,
Kandice
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question