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
Here's a query that the bottom listview in the attached form i.e. a listview representing a table of calls(many) to fims (1 top listview)
Code:
SELECT calls.id, calls.firm_id, calls.called, calls.said, calls.spoke_to, calls.next
FROM calls
WHERE (((calls.firm_id)=[firms].[id]))
ORDER BY calls.called DESC , calls.next DESC;
When I run the thing...I get a dialog asking me for firm id.
I want to change this so when I move up and down the firms LV (top)... the bottom LV updates taking firm id from the top LV with focus.
Access 2003.
How do I set up a date field so that it changes to the current date whenever any other field in the record is changed?
View 6 Replies View RelatedHow 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'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.
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.
Currently I have an issue where on of the fields in a userForm will not update. I have tracked down the problem to an update Event procedure
Code:
Private Sub txtRate_Change()
Me.txtSales = Me.txtRate * Me.txtPages
Me.txtGST = Me.txtSales * 0.1
Me.txtTotal_Inv = Me.txtSales + Me.txtGST
End Sub
The idea being, when you update the rate, the Sales/Revenue figure will update based on that rate. For a while this seemed to work fine. but recently , it just will not allow me to update the field txtRate, I cannot understand why. I have now replaced the _Change() event for a _LostFocus() event. but I am not sure that is as reliable, and I am still puzzled / worried as to why the _Change event will not work.I'm on Access 2013, win 7 , using a front end db connected to the back end using linked tables.
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.
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
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 RelatedHere are my tables
Question Table
ID
Question
AnswerID
CategoryID
Answer Table
ID
Answer
Category Table
ID
Category
So my form shows :
Question, Answer, Category
And I can display any existing question and make updates to the question and answer, that works fine. BUT, what I want to do is update which category a question is in. If I change the Category in the form, it updates the 'Category' in the Category table. What I want it to do is update the CategoryID in the question table.
I decide to add a new field to its related table. I always wait to create the form until I think my table is complete, but sometimes I just end up needing to add more info. Is there a quick way to update the form to include my new fields?
View 7 Replies View RelatedI have created a Public Function that would get a new Production Instructions number based off the [PI Number] of Tbl_Production_Instruction table.
I have a form that people will put in all information but the PI Number, then when ready they will click a button to update the PI Number. I place an unbound txtbox that will be hidden, with the control source to =NewPINum(), but when I tested the see if the unbound txtbox was populated with the new PI Number it was blank.can't figure out what I am doing wrong.
Code:
Public Function NewPINum() As String
Dim vNum As String
Dim strYYMM As String
Dim getnextPI As String
strYYMM = Format(Date, "yy") & "-" & Format(Date, "mm") & "-"
If strYYMM = Left(DMax("[PI Number]", "Tbl_Production_Instruction"), 6) Then
vNum = Right(DMax("[PI Number]", "Tbl_Production_Instruction"), 3) vNum = vNum + 1 getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")Else
vNum = "001" getnextPI = Format(Date, "yy") & "-" & Format(Date, "mm") & "-" & Format(vNum, "000")
End If
End Function