Greetings,
I have a form with a subform(sub1) that contains records. Also on the parent form is a subform that contains subtotals of the records on the (sub1) subform. When I add a record to the (sub1) form, I can't get the code to update on the subtotal subform unless I go to another record. what am I missing here?
Thanks!
O
Dear all,
Is there any way to update the same record from two forms? The requirement is that in a form I have a checkbox. If I select the checkbox, it will open another form, in which I can update some fields which are of the same record. When I tried, it is updating on another record. Is that possible to do so?
i am attaching a sample db . If anyone knows how to do it, please update the sample db or give me advice.
Thanks
I have one table (call it tblMax) which holds a number designating a maximum amount for items. I have a second table (call it tblItem) which holds the items current inventory, along with several other values about the item. Is there a way to reference fields from both of these tables on one form, AND have the record be able to update? I've used a query to bring them all to one form, but the record is not able to update using that method. The tblMax will be used for reference only, after the initial values are entered. I also tried leaving the form's Control Source blank and typing in Control Sources for each field, but was unable to get that to work. TIA.
View 1 Replies View RelatedOk so ive built a form that a user can use to enter in data that will update a certain table.
The table has for fields: Email, Password, Active and ID.
In my form ive got rid of the active and ID fields as i want them to update automatically when the user fills in the email and password and hits the submit button on the form.
The only problem im having is getting my head around how i would update my other two fields once the user clicks submit?!
i was thinking that i could simply code the submit button to add the relevant info into the fields eg onclick active = 1 and ID = previous id +1 but im completely lost
Any help or ideas?
on my form i have a few radio buttons, check boxes, text fields and combo boxes. i am able to change and update all fields except combo boxes. it modifies the wrong table...
combo_table
------------
id value
1 house
2 school
3 work
if on my form the combo box for a specific record shows house selected, the value of 1 (the id from combo_table) should be the value put in the main talbe...if this is changed to option 2, school, the main table should have 2 in that field...
what happens though, is if i change from 1 (house) to 2 (school) combo_table ends up looking like the following:
combo_table
------------
id value
1 house
2 house
3 work
I am using and UPDATE Sql statement to update a firld in a form to a table. However, I get the message updating 140 records etc. How can I get the SQL/Macro to simply update teh record I am currently working on?
Beeky
I constructed a database in access 2007, when you entered a new record the autonumber would be generated once you started typing in values. I have since upgraded it to an SQL back end and now the autonumber does not update until the recordset is completely entered. This is causing me serious problems with my subforms since the record will not update before I need to go to the subform.
View 1 Replies View RelatedHi
I have a database containing 3 tables (for now):
Customers
Orders
Catagories
Now i also have a form with customer information.
In that form is a listbox with a query statement, so that should display wich orders are placed by that customer.
SELECT Orders.[Order-ID], Orders.Description, Orders.Date, Orders.Category FROM Orders WHERE Customer=[Customer-ID];
Now this works for the first costumer record being displayed.
However when i view the next costumer, the listbox still displays the order information from the first customer.
How can i get the listbox to update itself when a diffirent record is viewed?
Thanks in advance
Hi all,
I have created a form to enter downtime information using the fields from downtime table (as follows)
tblDowntimes
fldDowntimeID (PK)
DateOccured
MaterialCode
ShiftID (FK)
LineMachineID (FK)
DTCategoryID (FK)
DTReasonID (FK)
However on the form, LineMachine is taken from the tblLineMachine where LineID and MachineID are FKs. This has a large list of machines as one line can have many machines and a machine may appear on more than one line
So on the form, i would like to select the LineMachineID by inserting fldLineID (which would be a combo box) where the user could select which line e.g. Line1 and then the combo box for the LineMachineID would only display the relevant machines for the entered line instead of all the machines for every line.
I managed to achieve this,but I am experiencing a problem where if, in the Line combo box, i choose a different Line e.g. Line2, the LineMachineID does not then display the machines on Line2, but keeps showing the machines for the line I originally selected (Line 1).
How do I get the LineMachineID combo box to update every time a different line is chosen in the LineID combo box?
Any assistance much appreciated!!
thanx all,
Keji
I have an orders Form with a subform for products. In the subform the user selects each product by first selecting the product category from a combo box, which filters the query for the second combo where they selects the product. Once this is done, I need the record to up date, but Access gives a run time error 3058: Index or primary cannt contain a null value.
In this case I belive the key field is the product_id form my 'order_detail_table' where this is a shared key.
I've been beating this to death (it's killing me!!) for a couple of weeks now and got nowhere. Maybe someone out there can have a look and see what my obvious mistake is ... (I've attached a cut down version of db with just tables, query and subform)
Thx.
Kev
Hi, I've tried everything to get this to work with access. I can get it to work with SQL but unfortunately in this case I can't use SQL. Problem is that when I try and update the record, I get an error saying that the update method is not supported or that the lock is incorrect. It's a 3251 error.
Here's the code I'm using:
Dim dbConn2 As ADODB.Connection
Dim dbRst2 As ADODB.Recordset
Set dbConn2 = New ADODB.Connection
dbConn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= R:myAccessDB.mdb"
Set dbRst2 = New ADODB.Recordset
dbRst2.CursorLocation = adUseClient
dbRst2.CursorType = adOpenDynamic
dbRst2.LockType = adLockOptimistic
Set dbRst2 = dbConn2.Execute("Select ROAD_ID FROM GIS_MSLINK_ROADID WHERE MSLINK" & " = " & UserForm2.slsn_textbox1.Value)
dbRst2.Fields("ROAD_ID").Value = UserForm2.slsn_textbox2.Value
dbRst2.Close
dbConn2.Close
Set dbRst2 = Nothing
Set dbConn2 = Nothing
Hi, ive got a form which requires a user to select atleast 1 option for a check box group.
when i pass the results of the form to access all of the checkboxes in the group are update although not all were selected by the user.
what can be causing this problem. The values are being passed to the correct fields in the DB but it still updates across the board.
Thanks
I have a form (frmHourEnter) with a textbox (Week_No_Calc) which has a calculation, in Control Source, based on another textbox. After exiting from Week_No_Calc how can I get a field (Week_No) in table (tblHourEnter) to be updated with the calculated value?
View 6 Replies View RelatedI have a separate button to update the record, but when I run or click the cmdUpdate button it gives me an error;
Code below is the one I'm working thru.
Private Sub cmdUpdate_Click()
strSQL1 = "UPDATE tblAMHMace " & _
" SET ReferenceNo=" & Me.txtReferenceNo & "'" & _
", DateLog='" & Me.txtDate & "'" & _
", DocType='" & Me.txtDocType & "'" & _
[Code] ......
I have a form that has a main for, a subform, and then another subform inside the 1st subform. The form works like this:
Conact info
--->Call info and notes
---> Orders worked
Each of those being nested as they appear. The problem is, the user usually jumps strait to putting the order number in "Orders worked" before entering in any notes or info... So no record exists in it's parent form to tie to. How can I have the database create a new record in its parent form when the user types in the subform?
Thanks!
I'm trying to update an imported table from an excel spreadsheet with missing details. The table's records are in order so I just need to fill in a blank field with data based on the previous one as shown.
ID Name Location
1 Bob London
2 Larry
3 Harry
4 Jerry Glasgow
5 Paul
6 John Southampton
I need to fill in the location blanks simply with the last location details, so records 2 and 3 with London and 5 with Glasgow. Is this easily done or would I need to pull all the data into an array and work on it there?
I've tried searching for an answer but haven't had any luck.
I have a form in which old customers can be edited and new customers can be added.
Currently, to edit a customer, the user clicks in the "Last Name" field and then selects EDIT-FIND and types in the last name. The user will then cycle through all customers with that name to select the correct one.
How do I allow a user to begin typing in the last name field and have the name appear? Is there an easier way to do this?
Many thanks.
In access (2003) I am setting up a table with Employee # (Pri. Key), senority numbers..and
so on. However I cannot figure out how to reset the senority numbers when one leaves. For example Bob is #235 senority and leaves (By retiring, quits or gets fired)..well Mary was #236 and now should be #235 (because Bob is no longer at the company)..and so on with everyone else below Bob getting their Senority number changed by one.>
Do you have any ideas..I tried autonumber, but it will not do that.
I have a text field "Record Last Updated" on a form formatted for date/time that I would like to update after a record is changed or added. So for every change or addition the field would update to the current date. The code I am currently trying to use is as follows:
'Assign current system date to Last Updated field if change of data occurs in any field
For Each ctl In Me.Form.Controls
If (ctl.ControlType = acTextBox) Or (ctl.ControlType = acComboBox) _
Or (ctl.ControlType = acListBox) Then
If Nz(ctl, "") = ctl.OldValue Then
Else
txtLastUpdated.Value = Date
End If
End If
Next ctl
This executes in the forms After_Update event procedure.
Problem is I get an error 3020 "Update or CancelUpdate without AddNew or Edit" when moving to the next record ? I have tried using .Edit and .Update but those come up as an invalid reference?
Any suggestions would be appreciated. Thanks in advance
Hi guys I am creating maintenance form to update and add new records to department table.
But when I try to edit department field in employee table.
(I get the following error.I get same when try to add new record.)
Error:
You cannot add or change a record becasue a related record is required in table department
http://i5.photobucket.com/albums/y1...gdepartment.jpg
==> pic of error errroupdatingdepartment
http://i5.photobucket.com/albums/y1...eletionship.jpg
===> timekeepingreletionship
I be happy if some expert help me how to update and add new record to department table successfully without the above error and without changing the db structure.
Thanks
I currently have a query that a subform uses to display some results. In this case, there are 3 columns displaying the name of a sandwich, the ingredients, and the number of servings of each ingredient. It looks like this:
SandwichName IngredientName Servings
Accordian Lettuce 1
Accordian Ham 1
Accordian Cheddar 1
Accordian Mayonnaise 1
That is the current output to the subform. The SQL statement I use to generate this list is here:
SELECT [TBL_Sandwich].[SandwichName], [TBL_Ingredient].[IngredientName], [TBL_SandwichContent].[SandwichTotalServings]
FROM (TBL_Sandwich INNER JOIN TBL_SandwichContent ON TBL_Sandwich.SandwichID=TBL_SandwichContent.Sandwi chID) INNER JOIN TBL_Ingredient ON TBL_SandwichContent.IngredientID=TBL_Ingredient.In gredientID
WHERE TBL_Sandwich.SandwichID=6;
I have bolded the last item the 'SandwichID'. It is this number I need to change dynamically based on a button being pressed on the main form. Essentially they enter a new sandwich, put the ingredients in, save it io the database, and I save the servings for each item as 1. Now I'd like the new sandwich to be displayed in the subform using the new ID of the sandwich which was just created.
If I try to put this long SQL statement in the RecordSource property I am told it's too long for the property to handle so I can't set this property dynamically. how to get the subform to requery with the new ID number of the item I just generated. I can get the ID number via DLookup, that's easy, but setting the subform to use this is my problem.
I'm having difficulty updating an existing record in my form. I can enter a docket number on my form and when i hit my button (Find Docket) it brings up all the customers info (name, date, invoice, vat, total, etc.) What i want to do then is be able to amend/update the record. Just wondering do i need to put vba code in my save button and how to code it and is it similar to my find data code below.
Here is my save function code:
Function SaveData() As Boolean
Dim db As Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Skips Delivered")
[Code] .....
I am trying to edit an existing record in my database.It goes to the correct record to update but will not update if "RecordUpdate" is specified as auto numbered.If the "RecordUpdate" is just specified as a number in the query it is fine.
CurrentDb.Execute "UPDATE QryIssueData " & _
" SET RecordUpdate =" & Me.txtRecordNum & _
", IssueDate ='" & Me.txtIssueDate & "'" & _
", Equipment ='" & Me.cboEquipment & "'" & _
", IssueDocNumber ='" & Me.txtIssue & "'" & _
[code]....
I have a tab control with a Counsellor Training tab on it inmy form frmCounsellors. The issue I am having is that when I select a training type from the drop down in the sub form on the tab control on the counsellor training tab and then go to a new record of the main form, not on the sub form, the training type I selected on the form stays the same. If I change it on the second record, it stays at whatever I changed it to, even if I return to the first record it stays at what I changed it to as well.
How do I get it to stay with the counsellor and have it zero out for a new counsellor and it to allow me to enter something different for the new counsellor then save it for that counsellor?I just realised, I am having the same issue with the location combo box but I am sure I could apply a fix to both boxes if I knew one.
Hey, I am trying to figure out how to update an unbound textbox on a form to reflect conditional data in other boxes when each record is viewed (pages works with reports but I cant find something similar with forms). such as if I had an unbound textbox that I want to = firstname+" "+lastname I would want for it to update it every time a user goes to the next record.. I can't seem to figure out a command or method that executes every time someone goes to the next record.. (and Im not too familiar with all the commands that come without a GUI).. thanks for your time
View 4 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.