Solved

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

Posted on 2014-02-08
10
1,386 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
Comment Utility
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_
Comment Utility
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
Comment Utility
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_
Comment Utility
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
Comment Utility
Sorry, I meant the BeforeInsert event.  Just like in the code I gave you.
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 1

Author Comment

by:Andrea_
Comment Utility
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
Comment Utility
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_
Comment Utility
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_
Comment Utility
Ron answered my question, and followup posts very quickly.
0
 
LVL 29

Expert Comment

by:IrogSinta
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

744 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

18 Experts available now in Live!

Get 1:1 Help Now