Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-02-08
10
Medium Priority
?
1,507 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 2000 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

926 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