Solved

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

Posted on 2014-02-08
10
1,404 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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
My experience with Windows 10 over a one year period and suggestions for smooth operation
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.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

867 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

21 Experts available now in Live!

Get 1:1 Help Now