473,322 Members | 1,526 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Help with automatically update a text box

418 256MB
I am trying to get the serial number of my items to be automated in certain way for my DVD Database. The form is called fItems, the field I want to be automatically updated is called iNo

I have two combo boxes tied to tables Language and Category to look up lID and cID


I want the format of the serial number to look like this: EG.FF.XX-nnnn . I have two letters associated to identify each item's Language and two letters to indentify its category. These two items come from tables Language and Category. In this sample, I can tell that the item belongs to English and it’s a Feature Film.

XX refers to the first two letters of the Title of the Item and nnnn refers to the actual ID of the item.

So for Guess Who’s Coming to Dinner, I will have: EG.FF.GU-0827 I would know that this item is #827 in my DB.

I was able to work out part of the automation, i.e., EG.FF. But I don’t know how to work out the remainder of the part. Also, the serial number (last four digits) are coming out ast 0001 for all new items added. I want it to pick it up from the actual PK.

Can someone please look at my Codes and help me out? Please be specific to mention about what to insert and where, if possible. I am, indeed a novice.

Thanks and regards.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.     If IsNull(Me![ProgramID]) Then
  3.       DoCmd.GoToControl "iTitle"
  4.     End If
  5.     Me![sfItemsJoin Label].Caption = "Casting: '" & [iTitle] & "'"
  6.  
  7.   With Me
  8.     If .NewRecord Then Call .iTitle.SetFocus
  9.     .lID.Locked = Not .NewRecord
  10.     .cID.Locked = Not .NewRecord
  11.  
  12.   End With
  13.  
  14. End Sub
  15.  
  16. Private Function GetCDKey() As String
  17.   Dim StrLng As String, StrCat As String
  18.   Dim intVal As Integer
  19.  
  20.   GetCDKey = ""
  21.   If IsNull(Me.lID) _
  22.   Or IsNull(Me.cID) Then Exit Function
  23.  
  24.   StrLng = Me.lID.Column(2)
  25.   StrCat = Me.cID.Column(2)
  26.   GetCDKey = "%L.%C.-%4"
  27.   GetCDKey = Replace(GetCDKey, "%L", StrLng)
  28.   GetCDKey = Replace(GetCDKey, "%C", StrCat)
  29.   StrCat = StrLng & "." & StrCat
  30.   intVal = Val(Nz(DMax(Expr:="Mid([iNo],8,2)", _
  31.                        Domain:="[Items]", _
  32.                        Criteria:="[iNo] Like " & _
  33.                                  "'" & StrCat & ".*'"), "0"))
  34.    GetCDKey = Replace(GetCDKey, "%4", Fmt(intVal + 1, 4))
  35. End Function
  36.  
  37.  
  38. Private Function Fmt(intVal As Integer, intDigits As String) As String
  39.   Fmt = Right(10000 + intVal, intDigits)
  40. End Function
  41.  
  42. Private Sub cID_AfterUpdate()
  43.   Me.iNo = GetCDKey()
  44. End Sub
Apr 30 '10 #1
0 983

Sign in to post your reply or Sign up for a free account.

Similar topics

31
by: da Vinci | last post by:
OK, this has got to be a simple one and yet I cannot find the answer in my textbook. How can I get a simple pause after an output line, that simply waits for any key to be pressed to move on? ...
4
by: Paul | last post by:
I have a form that is bound on a table. The form has a text box that has =Time() and an Add button. How can I update the time or make it the current time before adding a record on the table via...
2
by: MyNameIsnt | last post by:
Can anyone tell me why, when I click on the buttons it register 2 characters on the display? if you use the right mousebutton it works ok, but the buttons dont flash?? it works fine without the...
19
by: trint | last post by:
Ok, I start my thread job: Thread t = new Thread(new ThreadStart(invoicePrintingLongRunningCodeThread)); t.IsBackground = true; t.Start(); There are lots of calls to controls and many...
28
by: Siv | last post by:
Hi, If I run the following: strSQL = "Select * FROM Clients;" da = New OleDb.OleDbDataAdapter(strSQL, Conn) 'Create data adapter cb = New OleDb.OleDbCommandBuilder(da) ...
1
by: John | last post by:
Hi, I would like to update my form after something is intered into a textbox in real time. For instance, Box A has a blank entry and I enter in 5. Box B I enter in 6 so in Box C it should...
17
by: Timothy.Rybak | last post by:
Hello all, This is my first attempt at an application, so kid gloves are appreciated. I need to make a very simple form that only has a few elements. One is TraceCode - a text field that is...
6
by: Takeadoe | last post by:
Dear NG, Can someone assist me with writing the little code that is needed to run an update table query each time the database is opened? From what I've been able to glean from this group, the...
1
by: danxavier | last post by:
I successfuly installed dd.php and sajax.php files. It runs fine, but I would like to link the $items to an image. I called the field in mysql with the link "pic". Any help would be AWESOME!!! I've...
15
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.