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

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.
LVL 1
Andrea_Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
IrogSintaConnect With a Mentor Commented:
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
 
IrogSintaCommented:
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
 
Andrea_Author Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Andrea_Author Commented:
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
 
IrogSintaCommented:
Sorry, I meant the BeforeInsert event.  Just like in the code I gave you.
0
 
Andrea_Author Commented:
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
 
IrogSintaCommented:
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
 
Andrea_Author Commented:
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
 
Andrea_Author Commented:
Ron answered my question, and followup posts very quickly.
0
 
IrogSintaCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.