Solved

Access 2007 Updating a record using DAO from Form Button

Posted on 2010-09-01
6
543 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
  • 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now