One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database Option Explicit Private Sub AttachPaymentDetails() Call PerformInsert("tblFinancialBudget", "frmFinancialBudget") End Sub
I have a field and in front of all records in this feild I would like to add a fixed text. e.g. if the record is XYZ i want to add abc in front to make it abcXYZ. Sorry newbie here. ThaNKS
i have attached a document with print screen of the form and combo box properties i am working on
the way it works is, when the user selects a code, the appropriate desciption, uoi and price is displayed. but for some reason the price combo value is not being written to the price field in the table. all the data on the form except for item desc and uoi is written to tbl_waste. the item info for the combo boxes comes from qry_items.
please help..i have been banging my head for days now...thanks
When a customer makes a purchase I INSERT the transaction into the 'transactions' table, which I have no problems with. My problem stems from that I can't seem to get the 'OrderNumber' back from the 'transaction' table (OrderNumber is an AutoNumber by the way).
I have tried the following code - TempOrder = DLookup("OrderNumber", "transactions", "customerID = '" & Forms![frmOrder]!TempID & "'") "Forms![frmOrder]!TempID" is the value I use from another form (to see who the customer is). I want "TempOrder" to be the OrderNumber that is created in the transactions table.
I have a form and a subform. There are a number of buttons on the form, allowing me to add, update, delete, etc. records from the subform. The process is that I select a record on the subform, its details are displayed on the form, then clicking on <Delete>, for example, removes it.
Each of these processes work fine on their own and also if carried out one after the other, for the most part. The one problem I'm finding is when I delete a record.
Firstly, the subform appears to refresh and the record is no longer shown. However, if I then try to select another row from the subform, I get a message saying 'record is deleted'. I click on <OK> to remove this message and I can then select the same row or another row without any problems.
Secondly, if I now try to add a new record (the code behind the button is just 'DoCmd.GoToRecord , , acNewRec'), I get a message saying that I can't go to the selected record.
If I close and reopen the form between each activity, all works fine, suggesting that something isn't refreshing properly. I've tried putting 'Me.refresh' and 'Me.requery' immediately after running the deletion, but to no effect.
I'd have thought this should work but it doesn't.I just get a "Syntax error in INSERT INTO statement".
My date field, called "Date" (just in case that's a problem!) in my database has no input mask defined, it's just a basic date/time field. I've tried replacing the '#' symbols with single quotes, and doesn't work either. I haven't had any luck finding a definitive example of how to do this, even though it's trivial surely. All the similar examples I've found talk about how to update a record set with a date, or how to set the system locale, etc.
Hi, With a table in open view can a record be moved to a different position, i'm using access 2003. Also is there any way to sort besides ascending or descending? I've got a lot of tables to make and if I forget a single column/field and have to add it later (like I have done all ready numerious times) I'm almost having to recreate the table again in order to have the field fit in the correct spot in the table. These tables are bound to combo boxes so its important every thing is grouped for easy choosing.
The problem I have is, that I need to insert an apointment into de database but first I need to verify if there is a record or an apointment in that room that day the same hour, if that is so send a msgbox saying an apointment already exist in that room this day at this hour.
i tried using dlookup but it only works in one record using just one criteria
I have a form for entry and some fields are computed or result of a query from another table. I have a function that looks up a value from another table like so
************************************************** ******** Public Function GetTargetType() As Variant GetTargetType = DLookup("type", "tblFormulations", "[tblFormulations!formulation]=Forms![frmNmsConsumptionEntry]![formulation]") End Function ************************************************** ********
Which works fine when I test in the immediate window.Then I have this form event. This however does not insert this value when I am adding records using my continuous form.
************************************************** ******** Private Sub Form_BeforeInsert(Cancel As Integer) Me!target_group = GetTargetType() 'Forms!frmNmsConsumptionEntry!target_group = GetTargetType() '[tblNmsConsumption.target_group] = GetTargetType() End Sub ************************************************** ********
making sure I can insert this value once retrieved.
I have a main form where information for a particular part number or job is entered. Should the part number not yet exist, Access will let you know. I have a button within the main form that opens up the Form_EnterPartNumber form which allows you to enter the new part number.
What I would hope to be able to do would be to refresh (requery?) the main form after creating a new part number and closing the Form_EnterPartNumber form..........but, keep the main form on the current record instead of resetting back to record number 1.
I have a database that contains a few tables and queries etc. I made a copy of this database so that I could change some of the VB code with out affecting the database information. I have completed all the code and now I want to input those old tables into my finished verion. I am not sure how to do this. Any ideas on how I could do this would greatly be appreciated. Thanks.
Hi I really need to insert a movie in my database. I have tried inserting a Windows Media Player object in a form, but I dont know how to link it to a movie on my computer. Please help me with this. By the way I'm using DAO, if that helps at all.
Please help me as soon as possible, I'm in a hurry Thanks in advance
i am working on my company's database which is in access 97, and has a backend. the normal entry way when you add a column it wont save as it says it can't save propertys to a linked table. i added the required fields to the backend without any problems and saved and exited. now when i go into the "frontend" the fields arn't showing up on the table. any ideas? thanks briar
Ok, I know this is already part of another thread, but my problem is a bit more complex...
The database I've been designing catalogs traffic signal equipment at every intersection in a 12-county area. There's about 480 intersections in the database. My boss pointed out the need to have the intersections listed in geographic order. For example, if I have three intersections on IL Rte. 5 - John Deere Rd, Bauer Parkway, and Industrial Park Rd, they need to be listed in the order in which they exist on the road (from east to west or north to south). Alphabetically, the intersections would be Bauer, Industrial, John Deere. But, as you drive down the road (from east to west), the intersections occur as Industrial, Bauer, and John Deere.
I can sort the intersections according to their "corridors" without any trouble, but I have to force the geographic order of the individual intersections by assigning each an index value (like "1", "2", and "3"). Better yet, I can assign the indices in multiples of 10, 100 or 1000, (1,2,3 becomes 10,20,30 or 100, 200, 300, etc.) and guarantee there will likely always be a sequential position available in the geographic index for a new intersection. Plus, I can always pick the "middle" of the range for each new insertion. For example, if Bauer has a geographic index of 200 and John Deere's is 300, then I can give Zebulon a value of 250 - leaving equal room for insertions before and after Zebulon in the future.
Pulling this off isn't hard - I know how to do it. The problem is running out of positions in the index. In the case of my database, it is unlikely we'll ever add more than two or three intersections between two existing ones, so using multiples 100 is probably best. In this case, I can add at least 6 intersections before I run into the possibility of having two intersections whose indices have a difference of only 1. Using 1000, the value goes up to 9. Each multiple of ten gives you 3 more entries (minimum) before encountering consecutive indices. I guess the inherent problem is figuring out a more "infinite" way of accomplishing this. I don't readily need it, but on the off chance that I do end up with consecutive intersections (that I have to insert between), it'll require manual re-numbering to fix the problem.
That's my solution, but there may be something simpler. Any thoughts?
Can anyone tell me what i'm doing wrong with this insert statement?
INSERT INTO TBL_PROCESS_NAME VALUES( ' & [Forms]![frmIntroduction]![txtProcess_Name] & ' )
the form is loaded when the query is run, i've tried taking out the & and the ' but to no avail, all i get in the table is [Forms]![frmIntroduction]![txtProcess_Name]
The security table is made up of two primary keys: thing, personorgroup When i run this statement to insert into the security table
INSERT INTO security (thing, personorgroup, accessrights) SELECT '252600649', '4020', '255' FROM PROFILE WHERE not exists(select * from security where security.thing = '252600649' and security.personorgroup = '4020');
I get this error: Server: Msg 2627, Level 14, State 1, Line 25 Violation of PRIMARY KEY constraint 'PK_SECURITY'. Cannot insert duplicate key in object 'SECURITY'. The statement has been terminated.
Anybody know how i can perform my insert successfully? :D
i am trying to achieve a simple thing (at least it seems so to me!)... I am trying to add a new record to a table (LP_Product_Name) with only one field (Product_Name) as a part of a 'on Lost Focus' event of a text box (txb_ProductName). Code:Private Sub txb_productname_LostFocus()' Declare Variables Dim db As Database Dim rs As DAO.Recordset Set db = Nothing Set rs = Nothing' Assign Values to Variables Set db = CurrentDb() Set rs = CurrentDb.OpenRecordset("LP_Product_Name")' Enter New Product name to the table With rs .AddNew .Fields("Product_Name") = Me.txb_productname End With ' Close variables Set db = Nothing Set rs = Nothing' Restore Visible formMe.cmb_productname.Visible = TrueMe.cmb_productname.SetFocusMe.txb_productname.Visible = FalseEnd Sub When I type something in the textbox and shift focus to another field, nothing happens... no error message and no added value to the table! I dont know what is going on. It just doesnt work!
On the same form I also have a combo box that uses the same table (LP_Product_Name) as its rowsource. I want the Combo box to immediately show this added value.
HI, I would like to insert values to differetn tables using a form, is that possible with just access? or does it need VB script or SQL, if so, is there any example to follow. my form made of a combination of two tables attributes ad when I enter say:
name tel number address car car reg booking time
then I would need that info to go to the table it belongs to, as there is three tables, one is the car, the other is customer and the last is booking. thanks
Hi All, This should be a simple thing however Im having some problems. Im trying to insert a record into access. I can insert everything except the date. I cant seem to get the right format. Below is what Im trying: Code:strSQL ="INSERT INTO GISrequest (givenName, surName, email, division, title, purpose, audience, date) VALUES ('firstmname', "&strVal1&", 'anemail@somewhere.com', 'thedivision', 'thetitle', 'purposestring', 'audience',#" & AccessDate(NOW()) & "#)"
I think the error is in how I format the date, but Im not sure. Ive looked on the net for examples but I havent found anything that works... can someone suggest what may be wrong or perhaps provide (or point me to ) an example of inserting a date into an access database with ASP?
hEYA all, ive got a problem, im trying to inserting data from an array to my access table- but cant do this due to this error:-Data type mismatch in criteria expression.
Heres my insert code- which its genereating the error at: Code:sub InsertDBCmd( )'================================================= ========================================SET objRs = SERVER.createObject( "ADODB.recordSet" )dim sqlStatmentsqlStatment = "INSERT INTO Members "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "[A/C no], [Address_Code], "sqlStatment = sqlStatment & "[Managing Director], [Title], [Buying Group Member], [M D Sal], [Rebate Contact], " sqlStatment = sqlStatment & "[Rebate Title], [Rebate Sal], [Replied], [Member Name], [S/Holder 2], [S/Holder], " sqlStatment = sqlStatment & "[ALPHA TYPE], [Member Type], sqlStatment = sqlStatment & ") "sqlStatment = sqlStatment & "VALUES "sqlStatment = sqlStatment & "( "sqlStatment = sqlStatment & "'" & C & "', '" & No & "', " For n = 1 to 15 if n <> 15 then sqlStatment = sqlStatment & "'" & FieldsArray(n) & "', " else sqlStatment = sqlStatment & "'" & FieldsArray(n) & "' " end if Next sqlStatment = sqlStatment & ") "'rESPONSE.WRITE(sqlStatment)'RESPONSE.ENDobjRs.OPEN sqlStatment, objConn,1,3'================================================= ========================================end sub
I have two tables named datadistint and links and i need to insert only some values in five fields of links from datadistint but before inputting values we need to ensure they are already not there in links.
Insert Into links (LinkTitle,LinkURL,LinkDescription,maincat,cat) Select 1,3,2,4,5 From datadistint Where 1 Not in (Select LinkTitle From links) and 3 Not in (Select LinkURL From links) and 2 Not in (Select LinkDescription From links) and 4 Not in (Select maincat From links) and 5 Not in (Select cat From links)
i ran the querry as above but it doesn't work what should i do or what's wrong with this querry, because i need to compare all five fields and then insert only new values.
Hi! Please, please, please help me! I have created a form that should get Date, Day inputed by a user. Then it must place 8 the same records in the table. Any help will be appreciated Thanks in advance
