I have this code in Access VBA and it is not working.
Private Sub btnAddWorkingday_Click()
Dim strSQL As String
Dim strDate As Integer
Dim lastID As Integer
strSQL = "INSERT INTO tblSchoolWorkingDays (CALENDAR_DATE) VALUES ('" & tBoxDateToAdd.Value & "'); SELECT @@IDENTITY AS LastID;"
DoCmd.RunSQL strSQL
End Sub
I have a table that holds company information for the user - eg farm name, company name, manager, phone number and email address etc. Its simply used to provide headers for reports, so that the same generic database can be used on several farms. There is only one record in this table tblfarmdetails.The second table tblorder is for orders and receipts - I currently have code that allows the user to send an order request by email to the manager, and to also send notice of receipt of goods through to the manager. I would like the code to get the email address from the unrelated table.Can I reference this field in the vba?
Private Sub cmdemailorder_Click()
Dim orderdate As String
Dim stafford As String
Dim item As String
Dim itemamnt As String
[code]....
Hello I have another prob. I need to be able to take a new ID from the topics table and insert it into the messages table after a form has been posted. I haven't got a clue on how to do this.
Its for a message board where a new topic is inserted. Two tables need to have records inserted, the topics table and messages table. The Topic_ID from the topics table is an autonumber and needs to go into the TopicID field in the messages table. Here's my insert query:
sqlString = "Insert into Topics (Topic, BSection, Username, MesDate) values ('" &_
Topic & "', '" & BSection & "', '" & Username & "', '" & MesDate & "')"
Con.execute(sqlString)
sqlString2 = "Insert into Messages (Username, Member_ID, Topic, Message, MesDate, BSection) values ('" &_
Username & "', '" & UserID & "', '" & Topic & "', '" & Message & "', '" & MesDate & "', '" & BSection & "')"
Con.execute(sqlString2)
I've tried using the identifier command but that didn't work so I haven't got a clue on what to do. Can anyone help?
thanks
Grant
I have the following tables, tblAudit and tblCriteria. The first records companies and period of audit, the second contains the criteria against which they will be audited. The audit period determines which criteria apply.
Results are recorded in a third table, tblAuditResults.
Relationships
tblAudit 1:M tblAuditResults M:1 tblCriteria
My problem is when I try to add a new result by selecting a criteria via a combo box a new row is inserted in the criteria table as well as in the result table. Inserting a row in the criteria table is wrong.
I have attached the database for you to look at.
Thanks for any advice you can offer.
Fearless
I have a text box on my table form with the expression =([LINES]-[MISSED LINES])/[LINES] which gives me the results I want but I also want this total to go to the "On time %" column in my table.
View 3 Replies View Relatedi want to insert a calculated value of a e.g field6 (field4 * field5) in forms into the database table. by using expression builders in my forms. i have made that feild and on click it shows the calculated value but on filling in the rest of the form and clicking on Insert button does not update this calculated field but iserts all other fields.
View 3 Replies View RelatedI have elected to have tables inserted into my forms. For example on the School Information form, I have the Employees table inserted at the bottom so that it will show the employees that are assigned to that school when it's pulled up.
Before I split my database into a BE/FE situation, I could expand the items in the tables to other related items, but now that option is gone.
Is there any way to get that back with a split database?
I am just starting with access and I have a VERY stupid question. I made a combobox (it gets its values from a table with 2 colums the first the ID and the second is the products) when a user selects one of the products in the combobox the combobox is supposed to update another table with that value (and I mean the product name) yet what it updates is the ID # of the product that was selected!! How do i go around this.
I am new to access so i am not sure how things are done yet in access, i am pretty good in VBA in excel, but this is a new front for me that I am trying to learn. I did the above combobox using the wizard.
Layth
I have a subform generated by a query, I then have a double click function to run a further query this works when I open the subform separatly, but if I open the form and then double click on the field in the subform this will give me a error!
View 5 Replies View RelatedHello,
Im using a ASP and MS Access 2000 database. What im trying to do is: I insert a row into a table, in that table is a field that auto increments. after I insert this row I want to immediately after that be able to retrieve the value of the column containing the auto increment and hold it in a variable so i can use it for other things....
can anyone help me with this? I have been messing around with SQl cursors but cant seem to get it to work right..
Thank you very much
-Justin B.
Edit/Delete Message
New here, looks like a great resource.
I have a question about a form which has several fields on it. What I need to do is to capture the ALL the fields of the record I just entered(updated or inserted)
So, for example, if my form has fields for
RepID, RepName, Company
After I edit or insert a new record, when I click to the next line, is there a way I can use AfterUpdate event to capture ALL the fields that were just updated on the form, and do whatever it is I need to do with them? I hope I am amking myself clear. Thanks for taking the time to look.
I am inserting a new record into a 'master' table. Primary key is generated using a sequence. Now using that newly inserted primary key value, I need to insert a new record in child table. These 2 insert queries need to run at the time of deployment. how shall I store that primary key in a variable so that I can use it immediately in next insert statement?Can I use a returning into clause?
View 2 Replies View RelatedI have a table of nursery school children, names, addresses etc etc. One field is Under 2? Is there a validation rule (or something like) that will automatically put Yes (or No) depending on today's date and the child's date of birth?
View 6 Replies View RelatedWith the following VBA code, I'm trying to select the text value from a textbox and fill that in a query to select the code from a person. (a password only login).
However, I'm getting a run time error 3078 at the '' Set rs = db.OpenRecordset("strSQL")'' line.
Code:
Private Sub Tekst6_AfterUpdate()
Dim strSQL As String
Dim strBarcode As String
Set db = CurrentDb()
'strBarcode = Me.Tekst6
'MsgBox strBarcode
[Code] .....
I have created a From with some Fields: Name, Surname, Netto Price, Tax (%) and Gross Price..... to make a invoice.
The Problem is that i cant insert the data from: Tax and Gross Price in to the DB.I have tried this:
select Tax Field--> Property Sheet -> Control Source: =[Price]*0,19
example:
Netto Price: 100
Tax 19%-- =[Price]*0,19 = 19
Now I get the correct values displayed in Field Taxes.But this this sum cant not insert in to the Database.I use MS Office 2013.
Hi there,
I have used Calendar controls before but this is a new one on me?!
When i click on the Calendar, what i am doing is inserting the date clicked into a textbox on another form.
The Calendar is set to todays date the system date is also correct and although i click on 31/05/06, what is being inserted is 31/05/20 ?????
Any ideas, because i dont?
Thanks in advance
I am exporting a report from access. The problem is after export to .rtf, all fields appears correct except a bar code field (CCode39). It adds a space after the text which makes text non-recognizable by bar code scanner.If I use other formats, then there is no problem. I tried by using plain text as well rich text format for that particular field.
View 1 Replies View RelatedI have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate()
DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.
the data has a fixed part lets say
Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this
Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...
What is the best approach?
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next
[Code] ....
I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.
I have one table with the following field and data:
ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5
I would like to generate new records (in another table) based on the above fields and three records.Basically the end result should look as the following:
ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green
The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.
I have a database that has 2 tables. Table A and Table B. Table A is my primary table. On this table I have 2 fields. The first field is a LOOKUP Field that looks up information from Table B and displays my selection in the field on Table A. Then using DLOOKUP I automatically input the information in the Second Field on Table A based upon the selection from the First Field.
This is working mostly correctly. However, the problem is, when I click on the next record in the table, it automatically changes the Second Field on that record to the same value as the record before it and continues this trend each time I click on another record. This occurs without me making a selection in the first field. If I make a selection in the first field it does change the Second Field to the Correct Value, but then the next Record has the same issue.
How do I go about fixing this so it doesn't change the value with the change of the record. Only change if I change that particular field within that 1 record?is there a way to restrict the Value's in my lookup field to only include the Values from Table B that aren't already in Table A?
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 am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.
View 8 Replies View RelatedI have created a code below to test whether I can run a query and retrieve a data from an SQL server table. And so far I can return the result using a messagebox. but somehow I just don't know how to use this connection to update the table inside this access file. Basically I want to use this as a front end file. then when the form is open it will automatically update the table inside this access file and load the data to the combo box as a list.
Code:
Option Compare Database
Sub LocalServerConn_Test()
Set conn = New adodb.Connection
Set rst = New adodb.Recordset
[code]....
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code:
Dim strFile As String
Dim temp As String
Dim tbl As String
Dim db As DAO.Database
' error handle
On Error GoTo F_Error
[Code] .....