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 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 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 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.
Here 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.
Combo box on continuous form should have the control source listed as the field on the form that will be updated. The Row Source, however, is a query that includes 2 things: the field on the form that will be updated (this part will be nonvisible in your form) and the table/query of selections you want to show up in the combo box (visible). Then ensure the Bound column is set to 1 and the Column count is set to 2 with Column Widths as 0";1"
View 1 Replies View RelatedI am building a student register for a school for disabled children. There is a screen listing all students, one showing detailed student data chosen by #=IIf(IsNull([ID]),"(New)","Open")# clicking on "Open", one listing all the guardians, choosing a guardian by #=IIf(IsNull([ID]),"(New)","Open")# clicking on "Open", to diaplay Guardian details.
From the student list, I select and view student details, including a pointer to their guardian details. After selecting the first student details, and then viewing their guardian data, any subsequent student details and guardian details updates the prior guardian data with the next guardian's data.
How can I prevent the successive guardian details from updating the prior guardian table when it is exited? I view the guardian details using ADO recordsets populating a form. When I exit, it updates the previous record.
I have a navigation tab with 6 sections, of the 6, 5 of them are self-made split view with a form view on top and datasheet on bottom and upon adding records through a command button on form view I would like the datasheets to update.
On other forms it would (I believe) update to the bottom of the datasheet, now on the ones I've been banging my head against it either replaces the top record or doesn't show up unless I switch tabs and switch back. From my searches I assume this is in need of a requery...
I am having a problem updating a record on a table called BookInTable . The field I want to update is called Engineer and the record is found using the field Barcode. I have a form with 2 text boxes BarTxt and EngTxt as well as a button called SaveBtn. on the OnClick event of the SaveBtn I put the following code
Code:
Private Sub SaveBtn_Click()
If IsNull(Me![BarTxt]) Or (Me![BarTxt]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![BarTxt].SetFocus
Exit Sub
End If
DoCmd.RunSQL "Update BookInTable SET Engineer = " & Me!EngTxt & " WHERE BarCode ='" & Me![BarTxt] & "'"
End Sub
But this is bringing up the enter Parameter value dialog box for whatever I type into EngTxt. If I enter text into that box and then click okay it then updates to the record. how can I stop the from enter Parameter value dialog box appearing?
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.
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
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 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.