Modules & VBA :: Dmax After Insert Record
Aug 8, 2013
after I insert a new record using INSERT INTO and then use DMax() (in a private sub) to look up the new record. About half the time DMax() pulls the new record based on the primary key field (AutoNumber) just fine. However, half the time it pulls the max record prior to the new record being inserted. I.e. record 1001 was added and DMax() pulls record 1000. I'm assuming that my issue has something to do with the timing of when the record is writen/saved in the table. Is there a simple method of refreshing the table that I can use prior to using DMax()?
View Replies
ADVERTISEMENT
Oct 2, 2014
If a new record is created, insert date only if customerID is not null
Private Sub Form_Current()
If "CustomerID", "TblDietPlan" = <> 0 Then
If Me.NewRecord Then Me.MealDate = DMax("MealDate", "TblDietPlan")
End Sub
View 1 Replies
View Related
Jun 29, 2013
I am attempting to insert a record with selected data into a temp table and I am getting "Run-time error '3075': Syntax error in (comma)...". Here is the code:
Code:
Private Sub XferDataToTempTable()
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
[code]...
View 7 Replies
View Related
Jul 24, 2013
I just wondering if there is any way to Dmax more than one field? I found something and tried it but it does not seem to work.
Text13 = 10
Text15 = 5
I expected to get a result of just 10 but didn't
Code:
Dim Junk As Variant
Junk = DMax("[Text13] & ', ' & [Text15]", "Test")
MsgBox Junk
View 5 Replies
View Related
Oct 27, 2004
Hi,
I have a database / form with a hidden timestamp field [LastEdit]
that is filled automatically with the current time Now() when updated.
The purpose is that each time the form is opened I want it to "Goto" the last edited record.
I *think* I get around the find edit quotes because the smaller snippets below do work.
findit = DMax("[LastEdit]", "esn")
MsgBox DMax("[LastEdit]", "esn")
But the whole thing fails when I put it into one command as either one of these
DoCmd.FindRecord DMax("[LastEdit]", "esn"), , , acSearchAll
or DoCmd.FindRecord findit, , , acSearchAll
perhaps because of the quotes around the variable.
Any ideas?
Thank you,
View 4 Replies
View Related
Jun 18, 2013
I have a sub form with staff records on it within a main form. I am trying to allow the user to select a record from the sub form and add it to a table, here is my code which, to me, looks correct. However it gives me an error saying "Syntax error in INSERT INTO"
Code:
Private Sub Command3_Click()
Dim dbs As Database
Dim sqlstr As String
Set dbs = CurrentDb
Forename = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_forename, "")
Surname = Nz(Forms!frm_Capex_Submission!frm_staffSub.Form.shy_surname, "")
[Code] ....
View 1 Replies
View Related
Apr 15, 2015
How do i use DMax() function instead of Autonumber, since the table is empty and it wont know what to do with Dmax() + 1 for next record?
E.g I have a new table Customer_details which contains
customerID
customerName
Age
now if i enter data through forms, i want the CustomerID to be incremental for each new record, so i can use Dmax() function. But since the table is empty how can i tell the form, through VBA that if it is null store 1 else store Dmax("CustomerID", "Customer_Details") + 1
View 2 Replies
View Related
Sep 19, 2013
I need to use DMax to open the form on the last record based on the serial number. This code is what I have to open the form currently, but I want only the latest record related to the serial number:
Code:
DoCmd.OpenForm "Form1", , , "incoming_module_sn = '" & Me.txt_sn & "'"
Here is what I tried and it did not work:
Code:
DoCmd.OpenForm "Form1", , , DMax("incoming_module_sn", "tbl_module_repairs", "incoming_module_sn = '" & Me.txt_sn & "'")
View 11 Replies
View Related
May 2, 2015
I have a form - purchase, and a subform purchase detail they are linked with a field "puid"..I need the dmax to give me the maximum auto number field "pdid" on the subform in a textbox on the mainform.
I used the control source code:
=DMax("[forms]![purchase]![purchasedetailds]![pdID]","[forms]![purchase]![purchasedetailds]","[Forms]![purchse]![purchasedetailds]![puid]=" & [Forms]![purchase]![puid])
and the simpler:
=DMax("purchasedetailds]![pdID]","[purchasedetailds]","[purchasedetailds]![puid]=" &[puid])
View 5 Replies
View Related
Sep 23, 2014
I have a question concerning the automatically sequential numbering of a primary key as part of a composite primary key.
Tbl_treatment:
ID=numeric field (also in tbl_pt and in tbl_tumor)
Tumornr=numeric field (also in tbl_tumor)
Treatmentnr=numeric field
[ID] and [tumornr] are fixed and i would like to automatically number [treatmentnr] per [ID] AND [tumornr] in a way that the output will look like this:
1001 1 1
1001 1 2
1001 1 3
1001 2 1
1001 2 2
1001 3 1, etcetera.
However, when i use the SQL-formula below my output looks like this. It seems as if the function is not properly taking the composite primary key of [ID] AND [tumornr] into account or not finding the true max value:
1001 1 1
1001 1 2
1001 1 3
1001 2 2
1001 2 3
1001 3 3, etcetera.
If (DCount("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) = 0 Then Me.treatmentnr = fRowNum(False)
Else Me.treatmentnr = (DMax("[treatmentnr]", "Tbl_treatment", "[ID] = " & Me.ID & "" & "AND [tumornr] = " & Me.tumornr & "")) + 1
View 3 Replies
View Related
Oct 17, 2013
Anyway I've got a type mismatch that I can't figure out.
Code:
dMaxLstReq = DMax("reqNumb", "FlightLog", "Month([txtDate])='" & frmMonth & "'" And "Year([txtDate])='" & frmYear & "'")
In plain english: Select the highest value in the field named reqNumb from FlightLog where the Month of txtDate is equal to the variable frmMonth and the year of txtDate is equal to the variable frmYear.
txtDate is a Date/Time field in the table FlightLog
frmMonth and frmYear are both integer variables that take the system time (sysTime) and determine the month and year: I.E.
Code:
frmMonth = Month(sysTime) & frmYear = Year(sysTime)
Am I correct in thinking that the fact that my variables are integers and not times, that this is the cause of the mismatch?
View 2 Replies
View Related
Nov 11, 2011
I am using an Access form that I created on an earlier version of Access and I have been able to insert (add) new records with this form and database.
Recently, I added some text, moved some text around on the form, and saved the form.
Sense my revisions, I am unable to insert (add) new records. (The "New" button in the software's header is grayed out.)
I have saved and compacted/repaired the form/database, but this did not solve my problem.
View 4 Replies
View Related
Nov 4, 2004
May I insert a record into the top of a table?
Please help me! Thanks very much!
My email: jokelogpop@yahoo.ca
View 9 Replies
View Related
Jul 20, 2005
Hello
I have created a table of Some machines specifications.
Item Name Type location
1. Sieve156 Sieve 3rd floor
2. Rollermill1 Mill 1st floor
....
...
...
When inserting for example a new sieve machine I want database
add automatically this machine between items 1. and 2. and assign the item
number 3 to rollermill1. but this new record is added to the last row and its
item number will be 3 but it should be 2.
What shall I do for this problem? (in excel it is easily possible to add a row
between two rows.. is it also possible in access?)
thank you
View 6 Replies
View Related
Sep 27, 2007
Hiya,
I've inherited an Access 2003 database, a rather complex one. The database has a login, with the credentials stored in a table which I can view (minus the pass, which is masked). Of course, for security reasons, I shouldn't be able to just add a record and create a new ID, in essence bypassing all security. As such, the table with the user records does not permit new records to be added. What steps need I take to add new records, the option being greyed out in the interface, no new record bar available at the bottom of the table.
Any insight is most appreciated.
Slawek
View 1 Replies
View Related
Aug 15, 2014
In my VBA code, i am trying to move some table records from table1 to table2. Both table1 and table2 has exact structures.
In the INSERT statement for table2, what is the easy way to insert a record from table1? I am trying to avoid having to specify each field name in the INSERT statement. Because, i have 100 fields in table.
Is there a way to INSERT a particular record from table1 to table2 without having to specify field names in the INSERT statement like in below example?
INSERT INTO Employees " _
& "(FirstName,LastName, Title) VALUES " _
& "('Harry', 'Washington', 'Trainee');"
View 1 Replies
View Related
May 2, 2007
Using the sql profiler gives me no clue.
This is the insert query from the profiler.
exec sp_executesql N'INSERT INTO "ENVIS_GSD".."wat_springflow" ("result_dt","site_id","result_va","remark_tx") VALUES (@P1,@P2,@P3,@P4,@P5,@P6)', N'@P1 datetime,@P2 int,@P3 float,@P4 nvarchar(4),@P5 varchar(3),@P6 datetime', 'Feb 10 2009 12:00:00:000AM', 3000723, 8.000000000000000e-001, N'test'
The two records that I get the proplem on are not included here, but somehow they have been updated. I traced the access code, but I could see no reference to them that they were used for the update in either the form or any modules called.
Somehow up to a few weeks ago those two columns were updated.
Tracing the code and using the immediate window the user_name variable stores the login name corectly.
Someone mentioned "error 28" any ideas?
Thanks in advance
View 1 Replies
View Related
Apr 13, 2005
Hi folks,
I have an Append Query (Insert Into...) that I use to insert values into a table from a form. But to have that tie into other tables I need to gather the Student_ID (primary key) that is autogenerated when the record is inserted. How do I do this? I want to store it in a public variable. I've done quite a bit of searching but nothing of any value has turned up. Please help.
What I have on button press:
DoCmd.OpenQuery "Register Camp Students"
After that I need something to gather that newly inserted Student_ID and store it for later use. Please help!
Ciao,
jawilli1 :confused:
View 4 Replies
View Related
Jun 12, 2006
Hi guys. I hope someone replies to this thread....I am struggling with this one.
I am developing an Asset Management System for our IT Equipment.
We wish to record the software installed on each PC.
The Manufacturer and Software names are stored in separate tables.
The AssetMain table has a one-to-many relationship with the AssetSoftware table.
I originally had a (continuous) subform for software on my main Asset form. However, as I am using cascading combo boxes (to filter the Software Combo by the Manufacturer) it mean that changing the combo on each record would appear to change the existing values too (common problem).
I would therefore like four unbound controls.
1) Combo Box - Manufacturer
2) Combo Box - Software Package
3) Combo Box - Package Edition
3) Text Box - Serial Number
When the user selects/enters these values and clicks an "Add" button I would like add a new record into the AssetSoftware table where Asset_ID = Parent.Asset_ID.
I will then have a list box that queries the software for that particular Asset. This will requery on each CmdAdd_Click.
Attached is a screenshot of what I would like to achieve (if I click Add it should add Microsoft Office XP to the list box below)
Would really appreciate some help (do not normally get many replies to my threads :( )
Thanks
BF
View 7 Replies
View Related
Jan 31, 2005
So, I have a table with an autonumbered key field. I started the autonumbering at 1000 thinking that I may want to manually backfill some old records at a later date. Now I’m stuck.
Is there a way I can insert a new record into this table, filling this field as “950” for example?
View 3 Replies
View Related
Aug 29, 2006
Creating a flight management database to create flightplans that determine local/zulu times and dates of each leg of the flight. Arrival and departure times are calculated by durations entered for flight time to get arrival information, and time on the ground to determine departure time/date.
My question, is there a way for the user to insert a record (leg) into an exisitng flight plan that they are editing? The legs are currently in order by an autonumber so inserting a new record would not necessarly put the legs in the correct order of the flight.
Currently, the user only needs to enter the airport identifier and air/ground time along with initial departure time/date. No dates are needed for each leg.
Thanks
View 2 Replies
View Related
Apr 11, 2014
I have 2 table and the relation between the 2 table are [Booking_Num]
Code:
table A: [ID],[Booking_Num] [Prod_Name],[Qty_Order]
table B: [ID],[Booking_Num],[Prod_Name],[Date_Out]
when I get insert to table A, should be the table B get many record base on [Qty_Order]..?
sample:
Code:
table A
ID | Booking_Num | Prod_Name | Qty_Order |
01 | BOK001 | Hammer | 4 |
table B
ID | Booking_Num | Prod_Name | Date_Out |
01 | BOK001 | Hammer | Null |
02 | BOK001 | Hammer | Null |
03 | BOK001 | Hammer | Null |
04 | BOK001 | Hammer | Null |
View 1 Replies
View Related
Dec 4, 2013
I have two subforms: sf1 (Based on Table 1) and SF2 (Based on Table 2) in the first I have the fields and CODE QTY.
I need to insert into the second table, as many records as QTY. to enter a serial number for each unit of the item. For example:
TABLE 1
COD. QTY.
001 2
006 1
I must insert into Table 2
001
001
006
Dim num As Integer, contador As Integer
contador = 0
num = Forms![FacturasRec_NSF]![FacturasRec_Det_SF].Form![CANLFR].Value
Do While contador = num
Insertar
contador = contador + 1
[Code] ....
but I could not even.
View 6 Replies
View Related
Mar 24, 2005
Hello all!
I have a database that needs to post records that are joined from three different sources into a table. The query is done, and I get about 1,489 records out in 4 different states.
What I need to do is make a table with these records. Furthermore, it must be separated by state, whereas if there are less than 1000 records for each state, it must insert blank lines until it reaches then next thousandth (sp?) row (i.e. 1001, 2001, etc.), and then start posting the next state.
For example, AZ has 420 records. There has to be 580 blank lines before the query can start posting the next state, CA. At row 1001, CA starts posting, but there are only 200 records for CA so there must be another 800 blank lines before moving on to CO at row 2001, etc. etc.
Anyone have any ideas on this? Thanks!
View 5 Replies
View Related
Sep 6, 2006
Hi All,
I hope somebody can help me on this.
I still use Access 97.
I have 4 tables that contain a vehicle registration number field as their primary key and have one-to-one referential integrity applied.
I want to add a new registration to all 4 tables from one query. How?
I've tried many permutations with no success, such as putting the registration on a new table and trying to apply this to the 4 RI tables. I keep getting ref intergrity violations. Obviously I could disable the ref integrity rules, update the tables and then reapply the rules, but this is not possible if I am going to make the database available to a user group. A new registration needs to be added seamlessly. By not having ref integrity could leave the DB in an inconsistent state.
Help! If what I want is not directly possible, then any work-round would be appreciated.
Thanks in anticipation
Richard
View 1 Replies
View Related
Jan 2, 2014
i am using this query to insert the previouse balance into arrears as shown in the figure. it will insert some record correct and some are not correct.
SELECT fees.[gr no], sum((Admission+Registration+Fees.Tuition+[Fine]+Fees.Transport+[Exam]+[Misc])-Fees.Paid) AS bala
FROM fees
WHERE fees.balance<=0 and [gr no]=[gr no]
GROUP BY [gr no];
View 2 Replies
View Related
