Solved

Access 2010 Use query to update one field on data entry form

Posted on 2014-02-08
10
1,411 Views
Last Modified: 2014-02-17
I am new to Access and have a question that is probably quite simple for an expert.
I am using Access 2010.  I have a Data Entry Form (NewItem) which updates fields in tbl_Assets.  I have a query (qry_NewSerial) which finds the last serial number and generates the next serial number.
This is what I am trying to accomplish:
A user opens the form - NewItem.   A new record is created. The query automatically runs.  The value generated in NewSerial is automatically entered in either the form which then updates the ID field in the table, or in the ID field of the table which then displays on the form.

I have all the other fields on the form working the way I would like, and it does create a new record.  I'm just missing the way to link my query to the form, so the user does not have to guess a serial number.
Any assistance would be appreciated.
0
Comment
Question by:Andrea_
  • 5
  • 5
10 Comments
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39844511
I'm guessing that this a specific type of serial number and you don't just want to use an Autonumber field to generate it, right?  If so, can you give an example of a serial number?

Ron
0
 
LVL 1

Author Comment

by:Andrea_
ID: 39844542
Yes, the serial number is a number like 3124 followed by date such as 022014.   So it looks like 3124 022014.
I wrote a query which finds the serial of the newest record, takes the 4 digits on the left, converts them to a number, and concatenates that with the number + 1, space, MMYYYY.
If the next new Asset is purchased in March, it will create an ID of 3125 032014.
So, I need the query result to be added to a form which is based on the Assets table.
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 39844566
No need to use a query.  Just use the BeforeUpate event of your form to add the next serial number to your Serial field.  Use something like this:
Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.txtSerial = Val(Nz(Left(DMax("[Serial]", "tbl_Assets"), 4), 0)) + 1 & Format(Date, " mmyyyy")
End Sub

Open in new window

Ron
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 1

Author Comment

by:Andrea_
ID: 39844588
Thank you.   I thought there was probably a way, but I was having trouble coming up with it myself.

Just so I am clear...
I have an OnLoad event which has OpenTable - Assets table, and GoToRecord - Next.
Can I add this to the OnLoad event?  Or is it better to do it OnUpdate?

I would like the person entering the Asset information to see the new serial number, so they can create an asset tag.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39844597
Sorry, I meant the BeforeInsert event.  Just like in the code I gave you.
0
 
LVL 1

Author Comment

by:Andrea_
ID: 39844618
I'm sorry to keep bugging you, but I have another question.  In your sub routine, what part is telling it to place the value into the serial field of the new record in tbl_Assets?
I see how the sub finds the value, but I'm missing the update to the table.
You seem to know this quite well, so I thought maybe you could explain it to me.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39844632
I was assuming that your table was the recordsource for your form (NewItem).  Is this not the case?  Are you using unbound textboxes instead?  If tbl_Assets is indeed the recordsource of your form then do you have a bound field in that form for the serial number?  If you do, in my code I had it down as txtSerial but you would use the actual control name for the serial field.

No problem with the questions.  We're here to help.

Ron
0
 
LVL 1

Author Comment

by:Andrea_
ID: 39856406
Thank you for all your help last week.  You got me pointed in the direction I needed.
Here is the code that ended up doing what I needed.

Private Sub Form_Load()
Dim myR As Recordset
Set myR = CurrentDb.OpenRecordset("Assets")
myR.AddNew
myR![ID] = (Left(DLast("ID", "Assets"), 4) + 1) & Format(Date$, " mmyyyy")
myR.Update
Forms!frm_NewItem!ID = (Left(DLast("ID", "Assets"), 4) + 1) & Format(Date$, " mmyyyy")
End Sub

Andrea
0
 
LVL 1

Author Closing Comment

by:Andrea_
ID: 39856409
Ron answered my question, and followup posts very quickly.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39866543
The notifications for this question was somehow rerouted in my inbox to a different folder.  
Anyways, let me add a couple of insights.  First off, you can be shorten this one line to the following since your recordset is still open and current (that way you don't have two different places to make a change if necessary):
Forms!frm_NewItem!ID = myR![ID]
Or if the code is within the form you are referring to then you could use:
Me.ID = myR![ID]


Secondly, if your form is bound to the table Assets, then you could remove that line altogether and just bind your ID textbox to the ID field in the table.  Then after updating myR![ID], a Me.Refresh would show the new ID.  

Lastly, be cautious with using DLast.  It works fine when using it with a sorted query; however, it may give you inconsistent results when used with a table because of how Access stores records in its tables.  That's why I normally use DMax in these situations.

Ron
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

831 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