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.
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?
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!
I have a sub table that acts as a revision history for the items in my main table. I've just finished uploading all the records into the main table and now I want to insert one record into the subtable for each record in the main table to start the history with the record creation.
The sub table looks like this:
tblRevisions - RevID (auto Number / primary Key) - RevDate (date of revision) - RevName (Who made the revision) - RevDesc (What revision was made) - RevAuthorized (Who authorized the revision) - RevLabel (Foreign key to the primary table)
The info I want to insert is:
RevID - Auto Number RevDate - 11/27/2013 RevName - 3 RevDesc - "Added Label to database" RevAuthorized - 1 RevLabel - (One for each record ID in tblLabels)
I suspect that I want to start with an insert into statement, something along the lines of:
Code:
Insert into tblRevisions (Revdate, RevName, RevDesc, RevAuthorized, RevLabel) Values ("11/27/2013",3,"Added label to database", 1, ?)
I'm not sure how to indicate that there should be one label for each Record in tblLabels or that revLabel value should match the ID from tblLabels though.
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
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, "")
I'm trying to search through a table with serial card IDs, Order Numbers, and Ship Dates. I'm search through the Serial Card ID column and making my code work so that if the serial card ID exists and its ship date is between 9/30/2001 and 10/1/2011, then retrieve the associated order number and put in array. All of this works perfectly except for one tiny part : it's not returning a null for either unfound serial Card ID or serial card Ids that exist but their ship dates do not qualify. I would like to return a null value for these fields into the array as well as keep the found values in the array. The whole point of all of this is so I could count the number of rows for all the found order numbers and return a total value with that number.
I am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
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?)
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.
This one is driving me nuts. After doing quite a bit of research, I cannot find the answer that I need, so I am posting...
For a new record on frm1 I am trying to make the label (lblTest) for a text box named txtTest a certain color if a value has not been filled in. For some reason, I cannot get the code to work and I am guessing it is due to my lack of understanding of our good old friend NULL. Here is what in both the after update event for txtTest and the on current event for the form...
If Me.txtTest = Null Then Me.lblTest.ForeColor = 255 Me.lblTest.FontBold = True Else Me.lblTest.ForeColor = 0 Me.lblTest.FontBold = False End If
Why isn't the field red when I create a new record? Why is it that if I fill something into the field, then delete it, that it doesn't get bolded in red?
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 :( )
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?
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.
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()?
I'm hoping someone can help a noob with a simple problem:) I did a search but couldn't find the answer.
When a user clears a record and leaves it Null I would like to prompt them whether they would like to delete the record before the access error message about invalid use of Null comes up. If they don't want to delete the record then the code should undo the change. The problem I have is I can't seem to run my code without the access error coming up. I can't see where I can trap the error (err.Number 3398).
For example, this doesn't work:
Private Sub txtSalesItem_BeforeUpdate(Cancel As Integer) On Error GoTo Err_txtSalesItem_BeforeUpdate
Dim response as Integer
If IsNull(Me.txtSalesItem) Then response = MsgBox("Do you want to delete this category?", vbExclamation + vbYesNo, "Delete Sales Item?")
If response = 6 Then Runcommand acCmdDelete
else RunCommand acCmdUndo endif
End If . . .
I know I can put in a validation rule, but I would like to go a bit further. Does anyone have any suggestions?
Okay so I have a column called 'totalincome' on TblCashflow. There are 60 records of TotalIncome (represents 5 years or 60 months) per asset that I have on another table, TblLoanGeneral.
What I need to return is the LAST value from totalincome that is not null. For example, we may only have entries in the 60 records that go to row #35, or 50 or whatever. When I use the Last criteria it gives me the 60th record (in example would give me a 0), and when I use the Max criteria, it gives me the largest income value regardless of location(would be 50,000 in example). What I need is really where the "last" income amt is >1 (20) however, when you set last, it only considers row 60.In the example below I would want 20 to be returned.
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click() Dim strSQL As String Dim RevisionDate As String Dim RevisionRevisedBy As String Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I have a small database with 4 tables that I am using for the current problem. The tables are call, parents, mailman, orders. Call and parents are related by the call ID (a primary key in the Call table.). Mailman and orders are related by a Unique Id (a primary key in the mailman table.).
Forms involved are frmmain and frmsub.
Frmmain contains the call table information in the main form and parents information in the subform.
When a user enters a call with call ID and enters the operator name and parents information in the sub form, When a user clicks the OK button on the main form, necessary changes should take place if they enter the case type in the sub form part of parent information as ‘missing information’ or ‘missing link’ then the parent information with fields first name, lastname, case type, operator information should be inserted into mailman table in appropriate fields.
Simultaneously a record should be inserted into orders( after the record is first inserted into mailman, since both tables are linked with unique id) with the following information. Orderid being autonumber. Uniqueid from the mailman table. Orderdate system date. Ordertype should be “Mailman”
