Modules & VBA :: Store Values Increment By 1
Nov 22, 2013
I'm building a database to organize the editing of a massive report my office is working on. I've got a big table of all the sections. Each section has a unique ID and a version number. I wrote the queries so that they return only the most recent version (i.e. the Max version # of each section.) The results of the query appear in a subform.
I was able to use VBA to allow the user to double click on the record in the subform, and look at the record in another form. (I was really proud of this.)
Anyway, it turns out what I need is to double click on the record in the subform, and create a new record based on this record, but increment the version # by one.
I've created an unbound form for this, and googled around. I need to use VB to store the variable, and then put it in the unbound text box.
View Replies
ADVERTISEMENT
Sep 23, 2014
I am trying to perform a calculation within an IF then Statement. The difference is I need Access to remember a values to complete the calculations prior to setting the final answer. I think this is basic however I am a novice and can't seem to get it to work.
Code:
If [Forms]![Jobentryfrm]![StyleJobCurrentSub]![Assembly] = 39 Then
BL = ([Forms]![Jobentryfrm]![Height] * 2) + ([Forms]![Jobentryfrm]![Length] * 2) + 8
bw = [Forms]![Jobentryfrm]![Length] + [Forms]![Jobentryfrm]![Width] + 12
[code]....
View 4 Replies
View Related
Mar 12, 2014
Basically, what's the best practice or how do we store a query's value into an array then checking what the max or min value is and how to check if let's say "4" is in the array?
View 1 Replies
View Related
Feb 13, 2006
Hi
I have a numeric field called FileNo and an autonumber field called FileID in a table called tblFile.
I also have a lookup numeric field called FileTypeID (with values 1 or 2 or 3) on another table called tblFileType.
I need the value of FileNo which I am showing on a Form frmFile to change dependant on the value of the FileTypeID
i.e. If FileTypeID = 1 FlieNo should start at 100
If FileTypeID = 2 FileNo should start at 200
If FileTypeID = 3 FileNo should start at 300
Then when I create a new record I need to increment by 1 the value of FileNo according to the FileTypeID
I've setup a button and attached this code to its onclick event but it only works as long as I don't change the FileTypeID
Dim B As Integer
Dim H As Integer
Dim N As Integer
Do While FileID > 0
DoCmd.GoToRecord,,acPrevious
If FileTypeID = 1 Then
B = FileNo
Else
If FileTypeID = 2 Then
H = FileNo
Else
If FileTypeID = 3 Then
N = FileNo
End If
End If
End If
Loop
DoCmd.GoToRecord,,acLast
If FileType = 1 Then
FileNumber = B + 1
Else
If FileType = 2 Then
FileNumber = H + 1
Else
If FileType = 3 Then
FileNumber = N + 1
End If
End If
End If
ViRi
View 5 Replies
View Related
Dec 26, 2006
hello
I have set up the primary key as auto-increment through sql.
coursereference int AUTO_INCREMENT,
I have also entered VBA code for event handler below:
Private Sub Form_AfterInsert()
Me.Requery
DoCmd.GoToRecord , , acLast
End Sub
Every time when I delete the previous value , the next one entered is not decreased by one.
Could you please help how to set it up?
Thanks
View 3 Replies
View Related
Sep 29, 2005
I searched the archive for how to store a calculated value and found a lot of controversial dialogue about the pros/cons but not really a solution on how to do it.
I have a form based on a query. The database behind the form and query is our ERP database and is connected though an odbc connection. The form allows the user to enter some shipping dimensions and freight rates. The data is automatically stored in the ERP database and any user can view the data from the ERP software.
Here is the problem. There are also some freight calculations that occur on the form that our business teams want the results stored in the ERP database. They can view the results from the calculations using the ERP software instead having to open a 2nd application (Access form) to view the calculated data.
I have determined which fields in the ERP database will hold the data. I only need the vb code or other suggestions on how to update the calculated values into the database.
I apologize for the long message. Thanks for your help,
Jeff
View 3 Replies
View Related
Jan 11, 2006
Hi all,
I was thinking/hoping of using a list box on my form to store multiple values, I haven't been able to find a way of storing any value so far so not sure how easy it will be?
How can this be achieved or is it just easier to use several check boxes (approx 8)
Thanks
View 3 Replies
View Related
Jul 20, 2015
I am trying to get a value from a textbox into a table I created...
The textbox "calculates a number based on which radio button is pressed example if radio button 1 is pressed it returns 0 if radio button two is pressed it returns 8 and if radio button 3 is pressed it returns 15." I need to save the 0,8, or 15 depending on which radio button is pressed into the table...
I know you aren't supposed to store calculated values into a table but the report would never need to be changed and it just saves a new one each time with that being said anyway to make this work would be very useful!
Also how can you use the answer of calculated textboxs in other calculations?
View 14 Replies
View Related
Jan 24, 2014
I have the following code that updates the end date to be the same as the start date after the user tabs out of the start date. Instead of making it the same date, I want it to be six days later. How do I add that?
For example, user enters 11/10/13 in the start date. I want the end date to automatically update to 11/16/2013.
Code:
Private Sub StartDate_AfterUpdate()
Dim datDate As Date
If Nz(Me!StartDate.Value, #1/1/1900#) = #1/1/1900# Then
'The user removed the START data as criterion, so remove the END date.
Me!EndDate.Value = Null
[Code] ....
View 2 Replies
View Related
Jan 20, 2015
I have inherited an old database with a new field to add some new invoice numbers and increment the number to each recordset by + 1. Starting at 001. I have formatted the field to a Number Field and formatted it to 000"/2015". I now need to populate over 2000 records with the sequential numbers for each record.
I have created the following code as my attempt. Of course it doesn't work!
Code:
Sub AddNumbertoRecords()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim counter As Long
counter = 1
Set db = CurrentDb()
[Code] ....
View 7 Replies
View Related
Mar 31, 2014
I have a form that is being used for sample submission. Ideally, the user will be able to put in some information (3 or 4 fields) and the number of samples that they're taking, click the arrow and it will insert that number of samples into a temporary table that is then displayed on a continuous form. From there, they can edit samples, add more samples, and generate a report to submit to the genotyping group. Once their sample list is final, I will append it to the master list and clear the temporary table.
I've got the basics working. A looped "INSERT INTO" using a counter inserts the relevant information. I cannot, however, seem to get the numbering field to work.
Ultimately, the numbers need to look something like MySample-001, where MySample would be one of the other fields. Incrementing the 001 is the problem. In another situation in the database, I can successfully use DMax+1 to assign a single number on a single form, but I can't get it to increment the sample numbers.
Ideally, I'd like it to happen when the form is generated. However, I'm also open to having them assigned when the sample list is finalized, before inserting into master table.
View 8 Replies
View Related
Jun 29, 2015
User enters data relevant to placement on data card, in series of 20. Sometimes record lines are skipped on card, meaning user needs to be able to override default value, and then have code +1 for next record. For the output I need, Autonumber won't work, Recordsetclone won't work.
example columns:
weight/length/record
VBA code is set:
Private Sub record_BeforeUpdate(Cancel As Integer)
Me.record.DefaultValue = Me.record + 1
End Sub
User keying in a number activates the code, but won't recognize the default number.
weight length record
34 34 1 <----user enters
34 34 2 <--- auto populates from code
34 34 2 <---- auto populates from code, doesn't recognize previous record default set from code to output 3.
Any way to achieve the output I want?
View 3 Replies
View Related
Jun 5, 2013
I'm making up a stock system for the independent jewellers I work for. Each stock pattern needs an individual code which can quickly be recognised read as tickets are often taken off the jewellery and can be mixed up easily. To this end we categorise based on two criteria:
1. Material
2. Type
At the moment these are as follows:
MATERIAL
IDMaterial
0Base and Miscellaneous
1Silver
2Gold
3Palladium
4Platinum
5Pewter
TYPE
IDStock Type
0Previously Owned
1Ring
2Pendant
3Chain
4Necklace
5Bracelet or Bangle
6Earrings
7Brooch
8Gents
9Gift, Clock or Miscellaneous
So if I had three pairs of silver earrings I want the first to be No. 161, the next 162, the next 163. If I had three Gold Bracelets I'd want them No. 251, 252, 253 and so on...Material and type are both selected form separate Comboboxes on a form.
View 2 Replies
View Related
Sep 27, 2013
I have a simple select query , and I want to add a field that will take the value from another field and increment it based on a condtion.
i know i need a case statement but not sure how to go about this ..
column 4 is my desired result
i want to increment the text string from column 3 based on column 3 and restart the increment based on column 1
values of A would increment in the following manner 01,11,21,31,41 etc
values of O would increment 02,12,22,32,42,52,62
i'd also like the option that if a new value were introduced to increment it
values of x would increment 80,81,82,83,84
Example data .. apples and oranges ..
column1 column2(name) column3(type) column4
mary braeburn A 01A
mary blood O 02A
tom cortland A 01A
tom fuji A 11A
tom navel O 02O
tom mcintosh A 21A
[Code]...
View 2 Replies
View Related
Jul 8, 2013
I have a database that is used for tracking changes to numerous courses. Part of this database create a unique tracking number for each course problem developed. Currently I have the form do a comparison using the highest most number to compare against the current number assigned and prevent the form from saving until the number is incremented and not a duplicate, it would be a lot easier if I could just have it increment plus 1. I have seen various answers but they all seem to depend on the alpha portion of the field being a set value, in my instance it is variable in length. The only part that is fixed is the last four characters to the right which are the numeric portion I would like to increment. For example the field can equal:
QACP-M-PIQ-6059
QACP-M-PREF-6002
how to extract just the numeric portion, increment it by one and save?
View 2 Replies
View Related
Apr 23, 2014
I'm trying to use a form to store some usefull data on my database.
I'm using the DefaultValue property of the TextBoxes in this code:
Code:
Sub Comando17_Click()
Testo4.DefaultValue = """sasso"""
Testo6.DefaultValue = """sdr"""
Testo0.DefaultValue = Testo0.Value
DoCmd.Save
End Sub
Comando17 is the name of the button to run the macro
Testo4, Testo6 and Testo0 are the names of my TextBoxes
sasso and sdr are the values I want to set as Default
When I run this macro it changes the DefaultValue property on VBA local variables and the Value property, but the DefaultValue on the form structure remains unchanged.
View 7 Replies
View Related
Feb 20, 2014
I have a table like this :
Fields 1 --- field 2
A --- tuesday
A --- wednesday
A --- thursday
B --- tuesday
B --- wednesday
I want to end like this :
Fields 1 --- field 2
A --- tuesday,wednesday,thursday
B --- tuesday, wednesday
I was thinking of doing a loop in vba with recordset and a filter on field1 and concatenate the days of field2 in the first reccord of each letter.But my table is big so my code needs to be fast.
how to do this ?
View 3 Replies
View Related
Jun 10, 2015
i want to browse the files from a form and then store hem to the database.i have the following code, i can show the file path to the text box but i don't know how to store the file or how to upload the file to the database.
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
[code]....
View 5 Replies
View Related
Dec 11, 2013
I want to store a query into a table, which I will delete later on. But somehow it shows me an error: Data type conversion error at the qdf = CreateTableDef assignment line.
Code:
Public Sub LF_Query()
Dim i As Integer
Dim strSQL As String
Dim qdf As TableDef
[Code] .....
View 2 Replies
View Related
Mar 11, 2014
I have a folder in which there are PDF files stored. Now in the Form, there is a combobox and I want the code so that when a Form is loaded then add all those PDF file names(only first 9 letters of that) in the combobox.
e.g if the PDF file name is ABCDE1990-YYY then add ABCDE1990 in the combobox. So if there 10 PDF files in the folder then add 10 names in the combobox.
View 5 Replies
View Related
Apr 10, 2015
I want to use an array to store data from a list box into a variable. I want it to be able to store one value, or multiple values, depending on what is selected.
Main problem: this list box feeds off a table which has employee names and their e-mails. The list box itself only shows the names, and when I select what I want the array to store is their e-mails, not their names.
Code:
Dim strNames As String
Dim varItem As Variant
Dim intCount As Integer
For Each varItem In Me.lstNames.ItemsSelected
intCount = intCount + 1
Select Case Len(strNames)
[Code] ....
That code successfully displays the item I selected, but only displays the name. I need to make it look in the table and get me column #2. I also want it to be able to select more than one item at a time.
View 5 Replies
View Related
Feb 7, 2014
I am using this code
DoCmd.OutputTo acOutputReport, "rptFilter", "Excel97-Excel2003Workbook(*.xls)", strFilename, True, "", , acExportQualityScreen
With the OutputFile set to "" so the user can select the directory on where they want to store the exported template. I'm trying to figure out if I could get the file path and the file name and store it on a string so I could use it for something else.
View 4 Replies
View Related
Jan 28, 2015
I am writing the following code that will first of all display column headers dynamically using "Headers" field data from Access table and then find out the sum(volume) using column header and first column values. The following code works fine to display headers dynamically in Excelsheet from Access table but doesn't display sum(volume) in all the corresponding cells. As I can't attach the Access table so I have stored data from Access table to sheet named "Access Data" as attached. The sheet2 named "Report" should populate total volume .
Code:
Public Function Inputdata()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim i As Integer
[code]...
View 2 Replies
View Related
Feb 9, 2015
I have two tables
1) has email address, and Store ID
2) has multiple records per store
I need to write a procedure to send a separate email to each store that contains the records found in table 2 ( excel format).
View 3 Replies
View Related
Dec 5, 2014
I have the following dataset in a table called NR_PVO_120. How do i pick out a number (which can change but let's say, 6) of UNIQUE OtherIDs without excluding any OtherIDs under any fax numbers?
So, if you pick OtherID from Row7 you then also must pick OtherIDs from rows 8 and 9 because they have the same fax number. Basically, once you pick an OtherID you're then obligated to pick all OtherIDs that have the same fax number as the one you picked.
If the number requested (6 for this example) isn't possible then "the closest number possible but not exceeding" would be the rule.
For example, if you take OtherIDs from rows 1-10 you will get 6 unique OtherIDs but row 10 shares a fax with rows 11 and 12. You either need to take all 3 (but that will raise the unique count to 8, which isn't acceptable) or skip this OtherID and find one with a fax that has no other OtherIDs and that isn't on the result set already. My result of 6 UNIQUE OtherIDs will need to contain ALL OtherIDs under any fax the existing OtherIDs are connected to.
So one solution is to take rows 1-6, 26. Another is to take rows 1-4,10-14.
There will be many possibilities (the real dataset has tens of thousands of rows and the number of people requested will be around 10K), as long all OtherIDs connected to all faxes on the result set are part of the requested number (6 in this case) any combination would do.
A few notes.
1.Getting as close as possible to the requested number is a requirement.
2.Some OtherIDs will have a blank fax, they should only be included as a last resort (not enough OtherIDs for the requested number).
my table (NR_PVO_120)
Row OtherID Fax
1 11098554 2063504752
2 56200936 2080906666
3 11098554 7182160901
4 25138850 7182160901
5 56148974 7182232046
6 56530104 7182234134
[code]....
A few sample outputs
one solution is taking rows 1-6 and 26.
OtherID
11098554
56200936
25138850
56148974
56530104
56148975
Another solution is taking rows 1-4 and 10-14.
OtherID
11098554
56200936
25138850
56024315
56115247
56148974
This is for a fax campaign, we need to make sure no fax number is faxed twice, that all people connected to that fax number are contacted under one fax sent.
View 12 Replies
View Related
Dec 9, 2014
How can I add 7 days in todays date to store target date in the table?
Code:
ssql = "Insert into tblUpdate([Update_ID],[Date],Username,Status,Target_Date) values('" & j & "',#" & Format(Date, "mm/dd/yyyy") & "#,'" & k & "','Open')"
CurrentDb.Execute ssql, dbFailOnError
View 3 Replies
View Related
