Okay I've looked around but haven't found an answer to this, if there is one in the forum please forgive me. I've created a database to help track employee passwords for differnt system I placed and Audit Trail on the form (the example from Microsoft) however it updates only the form and not the table and I need it to do both. Please Help!
Thanks
Rand
I have a table called: Invoice which has Invoice No (as the PK), Invoice Date, Total Cost.
I also have another table called Task: which has Task No (as the PK) , Task Type, No of hours, Charge Amount.
The user enters the Details of Task and Invoice in a Form. I have a field called Total Cost of my Form which should Calculate =([No of hours]*[ChargeRate]).
The problem I have is the total cost on the form is not being updated on the Invoice Table with The Total Cost.
Can anyone help please
I have set up an ACCESS database of journal articles. Each article has one or more authors. I have created a form listing the article title, the name of the journal etc. Included is a subform listing each of the authors. The authors are listed in the order that their names appear in the article. I wrote a query to sort the articles alphabetically according the name of the first author (in the subform). The main form is based on this query so that all the articles are sorted according to the name of the first author. However, the query uses the “First” feature in the “Total” row on the query. The use of this feature precludes me from updating information in the form that is based on the query. Is there a way around this problem and if so what is it?
View 1 Replies View RelatedOk so here is my problem:
I have a form that is realted to four differnt tables with the names: CUSTOMER, ORDER, PRODUCT AND ORDER LINE. With-in the table Order-Line there is a entity called, line_item_cost whcih is the total amount of the order with respect to Quanity_Ordered which comes from the ORDER LINE table and Price which comes from the PRODUCT table and Discount (%) which comes from the CUSTOMER table. When the users goes to change the Quantity_Ordered I need the form to automaticly update the line_item_cost which would calculate [Price]*[Quantity_Ordred]-[Price]*[Discount] . I tried to build a code in VBA to do this, Here is the Code:
Dim a, b, c, d
a = Me.[Quantity_Ordered]
b = Me.[Price]
c = Me.[Discount]
d = a * b - b* c
Me.[Line_Item_Cost] = d
VBA Complies the program but it does not show up in my form.
I think I might be updating it at the wrong point.
Any Suggestions.????
hi
i am trying to show the total number of records that are in a table.
there are currently 8 entries in the table 'applications'
now i would like to show this on a tabcontrol in a form. I have been told to use Dcount. I have read about this and tried this on my form but unfortunately its not working.
Can anyone show me an example of how i would do this.
I am working with a database that I downloaded and am trying to modify to fit my needs.
This is an inventory database. The products table contains a description and pricing. I want the description and pricing to populate in the Purchase Order form, so I added Dlookup fields in the Purchase Order form. I was happy.
However, the pricing information is not populating to my Inventory Transactions Table from the Purchase Order form by way of this Dlookup feature, and therefore will not show on my report, and in turn does not show in my Total of my Purchase Order report.
As a work around, I tried creating a calculation in the purchase order report, of =[UnitsOrdered]*[Products.UnitPrice], and the pricing totals show fine on my report, but the subtotal doesn't work.
I was unable to upload my file...so a few notes of info...
There are no queries set up in the database for this report.
I had tried a sorting grouping thing (in the Report) by Subtotal, but now can't get rid of it.
When I show the field list for the report, across the top of the window reads:
SELECT DISTINCTROW Employees.*, Products.*, [Inventory Transactions].*, [Purchase ORders].*, Suppliers.*, nz([Inventory Transact
Looks like it runs out of space
I am trying to attach a couple of images to support my comments.
Since this issue crosses both reports and forms (and tables!), I am not sure where to properly post. The end result I am looking for is on my report.
I am using Access 2003...
How would I go about updating a table from a form? My table consists of employee contact information, ie.. Cell phone, pager number, home phone.
I have created a forum with unbound combo box called employee. As I choose the employee name all other fields are populating just as i want. After I update the form with the correct system contact information i want to be able to save the data and have it update the table. It appears the only data not updating the table is the anything in the combo boxes that are unbounded. Any suggestions?
If you open the attached sample db you will see that I have a cascading combo box (It is working fine).
The data for the tables that are the source for the combo box are imported from an excel sheet.(This works fine)
Now my problem the data from excel is variable after the data is imported into access I want to add to it items from another table.
After the data is imported into both tblModel and TblParts I want to append to tblParts each item from tblFixed for every Model.
If you open tblPartsEXAMPLE you will see what I want to do (I added this manually).
I hope that my expanation is clear and someone can offer some advice.
Thank you in advance.
alright I have a form and I need a couple of text boxes on there to add information to a different table than the other ones. I found this code somewhere on this forum can you tell me if I can use it for what I need, A more detailed explination of my project might be nessecary.
Code: sub AddRecord_Onclick docmd.setwarning false if not isnull(txtName1) then docmd.runsql "INSERT INTO table (areaname) VALUES('" & txtName1 & "');" txtName1.Value = Empty endif end sub
what is the proper syntex for the Insert INTO part, Table is obviously the table I want it inserted into, what is (area), is this the field I want it inserted into?
I want to create a form that will update a table. I have two columns, an item number and a paid column. On the form I want to plug in the item number and then I want to hit ok and it will find that item number and put a value (for example "X") in the paid column.
I have tried running an update query when i hit the ok button but I can never get to work
I have an access form (Customer) along with a sub-form (Work_done). On sub-form I have Running sum of Amount in Text box-1. I want that Total of Running sum be replaced with Main form's tables field total_amount. Is it possible that we can replace an amount of control of sub-form into main form's field?
View 1 Replies View RelatedI've creating an Access project, which is at an early stage. I'm using Access 2003 with a default file format set to Access 2000.
I've been trying to update a date field within a table using this piece of code from a Calendar form. The Calendar form has a Command Button called cmd_Accept. Its sub routine is shown below. Both variables, dt (date) and fvr_id (field visit report ID) are global.
The code works - too well! Instead of updating the only the target record, it will either update the first record plus the target record or all records with the same date and I cannot understand why this should happen.
[Field Visit Report ID] is unique, being the primary key for the table, tbl_field_visit. It is AutoNumber generated. I've double-checked the table and all entries in [Field Visit Report ID] are unique. All my MsgBOX displays show what I would expect to see.
As you can see, I've also tried doing the same task using SQL - with exactly the same result.
I've been going round and round with this for days. Should I bin this and go down another route, or can anyone tell me where I'm going wrong?
Any help much appreciated.
Private Sub cmd_Accept_Click()
Dim dbs As Database, rst As Recordset
Dim strSQL As String
dt = ocxCalendar.Value
' +++++++++++++++++++++
' Field Visit Report ID now set from another sub routine. Tested and working.
' fvr_id = Form.[Field Visit Report ID]
' +++++++++++++++++++++
' MsgBox "The Field visit report ID is " & fvr_id, vbOKOnly
' MsgBox "The date is " & dt, vbOKOnly
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_field_visit")
If rst.RecordCount <> 0 Then rst.MoveFirst
MsgBox "fvr_id = " & fvr_id & " Date = " & dt, vbOKOnly
Do While Not rst.EOF
If (rst![Field Visit Report ID] = fvr_id) Then
MsgBox "The Record ID is " & fvr_id, vbOKOnly
found = True
With rst
.Edit
!Date = dt
.Update
.Bookmark = .LastModified
End With
End If
rst.MoveNext
Loop
rst.Close
' If found Then
' vbButtons = vbOKOnly + vbExclamation
' MsgBox "Record Found! " & " Date = " & dt & " Field Visit Report ID = " & fvr_id, vbButtons
' If rst.RecordCount <> 0 Then rst.MoveFirst
' strSQL = "SELECT tbl_Field_Visit.Date FROM tbl_Field_Visit WHERE tbl_Field_Visit.[Field Visit Report ID] = " & fvr_id
' Me.RecordSource = strSQL
' MsgBox strSQL, vbOKOnly
' strSQL = "INSERT INTO tbl_Books ([Date], VALUES '" & dt & "')"
' strSQL = "UPDATE tbl_Field_Visit SET tbl_Field_Visit.Date = '" & dt & "' WHERE tbl_Field_Visit.[Field Visit Report ID] = " & fvr_id
' MsgBox "strSQL = " & strSQL, vbOKOnly
' dbs.Execute strSQL
' End If
dbs.Close
Set rst = Nothing
Set dbs = Nothing
fvr_id = 0
DoCmd.Close
End Sub
Can anyone help?
I'm having trouble storing autofilled data from one of my forms and I was wondering if anyone has any ideas.
My data is as follows
tblProducts
ProductID
Description
Nett
tblPurchaseOrders
PurchaseOrderID
PODate
Supplier
tblPurchaseOrderDetail
PurchaseOrderID
ProductID
Description
Qty
Nett
Now I have a standard order entry form which works fine, my problem is with the subform:
frmPurchaseOrderDetail
ProductID
Description
Qty
Nett
On my form Description and Nett are auto populated referencing ProductID but this information does not transfer to the table tblPurchaseOrderDetail. The main problem is that a nett price can occasionally chanege (inflation) but I need to be able to look back to a particular order for audit purposes and be able to see what I paid.
I'm sure there is w way round this, but I've been unable to figure it out. I've gone through loads of different threads on the forum and as a result am sure it can be done, I've also looked at the examples posted by several users, but none seem to approach the lookup quite from this angle, does any one have any idea...sorry for the long post, thanks.
Hi all
I'd be very thankful if someone can please help me with this problem.
I have 1 Table. This Table is imported and contains data from Excel. It also contains fields I added manually. There is about 20 imported fields (full of data) and about 100 manually added fields.
Then I made a Query of this table. I made some calculations in this Query.
I then created a Form based on the Query. I then made a Combo Box. This Combo Box selects a customer, and then updates the other fields (the ones I imported from Excel) depending on the customer I selected.
So now what I want to do is this: I want the fields (updated from the query) and newly entered data to save to another newly-created table. Can someone please help me with this? Any ideas?
Thanks a lot. All input greatly appreciated.
kruger101
Help - I'm at a loss! I have a form that I am using for invoicing for a Trucking firm. On this form, I have three fields that I am having problems with. These fields are From, To and Mileage from a table named Location. The user selects "From" from a combo box then goes to select "To" from another combo box. The "To" field is a filtered list of only those locations which correspond to the "From" that was chosen. Once this is done, the corresponding mileage is automatically filled in and used for rate calculations. If the "To" that they want is not in the table, they can double click the field and a form to add a record to the table pops up. So far, so good.
My problem is that now the user wants to be able to add a record such as New York to be From and Miami to be To with 123 as mileage once and be able to have Miami as From and New York as To with 123 as the mileage if he needs on future invoices. Can this be done without having to enter it again?
I have a form to enter records in a table of staff attending training courses, in the form is a sub-form based on a query from a different table. When a staff number is entered on the main form the staff name grade etc appears on the sub-form, my problem is that I need the table to be updated from the main form and the sub-form. Is there a way of doing this?
Thanks
I have a database with nine tables, and forms for data entry, no subforms. It is created for students to track all work required to complete their degree. When student information in added for a new student I need to update other tables with their student id.
The student information table has student ID defined as autonumber. How do I update the other tables with the value generated here, and should all the tables with student ID be defined as autonumber, or some other data definition?
Thank you.
I need to add more entrys to my form, but when I go to the ref. table it won't let me. I states too many fileds defined.
Another table lets me add to it but when I go to the form it does not show what I added.
Hope I make sense.
Thank you in advance
Greetings! This is my first post.
I have two tables: 1) tblClient, 2) tblCase.
Client records from tblClient contain a field called Client_CID (Primary Key), as the Client ID. There are also fields Client_HIGH_FILE_NO, a numeric value of the last case number assigned to the specific client and Client_PREFIX that contain a unique three letter prefix that identifies the client.
Case records from tblCase contain a field called Case_CFN (Primary Key), as the Case File Number. tblCase also contains a field called Client_CID that contains the Client ID associated with the case (obtained from a combo box lookup from tblClient).
My form is frmCase bound to tblCase.
The Case_CFN is constructed by combining the value of the selected Client_CID’s Client_PREFIX with the value of Client_HIGH_FILE_NO plus 1.
I am constructing the Case_CFN on the before update event of the combo box for selecting the client. The resulting Case_CFN may appear as follows
ABC10001
Where Client_PREFIX = “ABC” and Client_HIGH_FILE_NO = 10000
Now, I need to increment Client_HIGH_FILE_NO in tblClient by 1, meaning I need to set the value of Client_HIGH_FILE_NO for the selected Client_CID to, in this example, to 10001.
Questions:
1) Is anyone familiar with this type of number scheme generally and if so any ideas?
2) Can anyone tell me how to update the value Client_HIGH_FILE_NO for the selected Client_CID?
I have a table holding clients data, I need it to work out the age of someone when an application is made, which I would like to be stored on said table. I have two fields [DOB] and [signed date], which I have used created a query with and an (unbooud?) field called age at application with the expression =DateDiff("yyyy",[DOB],[Date signed])
This works fine when I run the query, but I am unsure of the new next step of how to commit it to the table and even if that's possible.Ideally I would like this to run behind a form maybe using some click event after the [signed date] field has been entered.
I have a form that has combo boxes and text fields (as well as sub forms). There is also a button linked to some code that says'
Private Sub cmdQuote_Click()
'Creates quote date and prints quote
Me.QuoteDate = Now()
Me.cbAgentID.Requery
DoCmd.OpenReport "Quote", acViewPreview, , "BookingID = " & Me.BookingID
End Sub
When the button is pressed the QuoteDate field (it is bound) should be be populated, but unfortunately it is not. I have played with refresh and requery but cannot derive a solution.
Hi,
I have two tables, tblCountry and tblLocation. With the following structure
tblCountry
ID
Name
Text
tblLocation
ID
Country_fk
Name
Text
As you can guess tblCountry lists all of the countries, tblLocation lists all of the locations in each country, the tblLocation.Country_fk field is linked to tblCountry.ID.
I want the user to be able to edit [tblLocation].[Text] using a form. They simply select the country and then the location using combo-boxes and then add or edit the content using a textbox.
In my form I have a combo-box that displays the country names, the RowSource is set to
SELECT [tblCountry].[ID], [tblCountry].[Name], FROM tblCountry ORDER BY [Name];
I then have a second combo-box that lists the locations for the selected country. This uses an AfterUpdate() procedure to select [tblLocation].[ID] using an SQL query based on the value of the country combo-box. I.e:
SELECT [tblLocation].[ID],[tblLocation].[Name],[tblLocation].[Text] FROM tblLocation WHERE [Country_fk] = " & Me.country_box.Value & " Order By [Name]"
I want to be able to have a textbox that then displays [tblLocation].[Text] for the selected location. Thats where the problem arises. I can't find a way that will let me display any content thats available for the selected location AND let me edit it. I've tried using UpdateAfter procedures, different bindings (tables, queries based on the value of location combo-box).
Can anyone suggest how I can display [tblLocation].[Text] based on the value of the selected country/location and be able to update the information via a textbox?
Any help would be appreciated!!!
Thanks
Jon
This is my first post, but I've been lurking for sometime. I'm grateful for all the great advice given here; despite my efforts, I can't find anything directly related to what I'm doing, though.
I have a form that is populated from a query. The query has some calculated fields and some direct selection fields from a couple of tables. One of the direcly selected fields is one that I'm trying to populate from the items in a list box.
On this form, there are two list boxes, List1 and List2. The user makes selections in List1 and clicks a command button, which runs code so that the second list box is populated with the items from List1. This was shown here:
MS Article (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnofftalk/html/office03022000.asp)
I actually just used this code and made changes accordingly so that this feature is working perfectly. However, the items in List2 need to be updated into a table's field, and this is where I'm having the problem.
I've got List2 bound to the proper field in the query, and I can manually run that query and make changes in that field fine. What I can't figure out is why I can't get the ItemsData property of the List2 control in there. For ease of code, I've added a line that copies the List2 rowsource variable to another variable so that the values can be used elsewhere. I can't seem to get the field to receive the variable in VBA, and I can't figure out how to get the values back into the query so that the query's source table is updated.
Any clues? Or is this unclear? I'm happy to give any further information. I've been working on this for more than a week, trying different things, and I'm at wits' end.
How would I update my cash account account table through a form? I have just started working on access and am fairly new to it.
What I am trying to do is to update my master table with all the daily sales through a form. Would I need to write a query into my form?
I have a table which is used to store info regarding medicines dispensed. I also have a corresponding Form to enter data. The fields in Table are
ID- number
Dispensed Date- date with dd/mm/yyyy format
Dispensed Type- text
Quantity- no. of days
Next Collection Date- date with dd/mm/yyyy format
The Form also contains same fields but it has a calculated field for ( Next Collection Date) where i calculate date using Dateadd function. Also the form has a Datasheet view. So records are added when I press Tab or Enter at last field.
Now the problem is the calculated dates arent getting updated in the table. And this is a huge problem as i have to run a query later where i will put a criteria on Next Collection Date.
A textbox on a form concatenates 2 strings. I want to insert that resulting string into a table .how can i do that ?
View 1 Replies View Related