Using SQL To Update A Subform -- WHERE Problem

Jun 9, 2005

I'm trying to pass a value from my main form to my subform for the related records when the user clicks a button. The problem is that it either wants to update the entire table or can't "see" the value on the main form.

Here is my code

DoCmd.RunSQL "UPDATE [tbl_req_order] SET [process_status_rec_id] = [req_process_status_rec_id] WHERE req_rec_id = Me!req_rec_id;"

I'm a novice, so please bear with me. It works, but it always asks me what the value of Me!req_rec_id is. That's the primary key in the parent table that is linked to the subform. How can I tell it to use the value for the form the user is on?

Thank you!

View Replies


ADVERTISEMENT

Modules & VBA :: Update Subform Requery Other Subform

Oct 5, 2013

i have a main form with three sub forms on it. when i update subform 1 i want subform 2 and 3 to update..currently i have an after update event in a combo box subform 1

Code:
[Forms]![FRMDATESUM]![frmHOURSUMnowork].Requery
[Forms]![FRMDATESUM]![frmHOURSUM].Requery

when the user updates the combobox in subform one nothing happens to the other subforms until close and reopen main form

View 2 Replies View Related

Modules & VBA :: Subform To Update A Different Subform

Jun 6, 2013

How could I write vba in order to get one field in a subform to update another field in a different subform?

View 2 Replies View Related

Can't Update Subform

May 15, 2006

I moved this to it's own thread since it wasn't the same question as was posed by the thread creator.
Pat

Not really the same issue but I thought to save us all from another thread...I have three subforms on a main form which are linked master/child. When I go through records on my first sub other two should be showing the additional info. I'm using requery as an form_current sub and works well with one sub, but doesn't work right with the other. The thing is that it shows the data on the form only if there is some data in the table already, but not if you want to add some new.

this is the code:
------------------
Private Sub Form_Current()
Dim ParentDocName As String

On Error Resume Next
ParentDocName = Me.Parent.Name

If Err <> 0 Then
GoTo Form_Current_Exit
Else
On Error GoTo Form_Current_Err
Me.Parent![tblInfoSub].Requery
Me.Parent![tblInfo2Sub].Requery
Me.Parent![tblKategorije subform].Requery
End If
-----------------------------------

since I am actually getting requery to work...what else might be the issue

tnx a lot

Daniel

View 1 Replies View Related

Update Subform Records

Sep 29, 2005

I am building a debt management program in A2k. I have created a tabbed control which contains 6 pages based on a set of queries. These pages are a budget template and the intention is that when this form is inserted as a subform into the customers form the budget will open as a blank template with the budget categories displayed. The clients budget will then be entered and saved. The Budget table contains 56 budget categories.

At the moment when I insert the budget form and use it the only records that change are the template. I want the first 56 records to remain with zero values so that they display as blank for a new client but I want the total 56 new records to have the customer ID associated so that when that client record is accessed again their budget will appear. I have attached the SQL code for the query that displays a sub portion of the budget.

SELECT Budget.BudgetID, Budget.ClientID, Budget.SectionNumber, Budget.BudgetTitlesID, Budget.Description, Budget.Cash, Budget.Credit, Budget.Frequency, IIf([frequency]=1,([cash]*52/12),IIf([frequency]=2,([cash]*26/12),IIf([frequency]=3,([cash]),IIf([frequency]=4,([cash]/12),0)))) AS Totalscash, IIf([frequency]=1,([credit]*52/12),IIf([frequency]=2,([credit]*26/12),IIf([frequency]=3,([credit]),IIf([frequency]=4,([credit]/12),0)))) AS Totalcredit
FROM Budget
WHERE (((Budget.ClientID)=[forms]![customers].[clientid]) AND ((Budget.BudgetTitlesID)=1)) OR (((Budget.ClientID) Is Null) AND ((Budget.BudgetTitlesID)=1));

My thought was to do an after update event that would copy the clientID from the main form to the sub form and populate all the records in the budget. However, I have only succeeded in populating the individual record that has changed.

I probably haven't explained the problem sufficiently to get some help but would appreciate any thoughts.

Thankyou

View 2 Replies View Related

Subform - Cancel Before Update

Jul 26, 2006

I have a subform which asks a user to confirm the update before it happens - if the user selects no - then the update is cancelled.


When the parent (they are not related record wise) form is closed the update on the subform is attempted - if the user selects no - how can I cancel the close of the parent form. At the moment a message saying "Cannot save the record at this time - changes may be lost". Which I don't want to see.

Thanks

View 2 Replies View Related

Can't Update Record In Subform

May 25, 2005

Hello all! I'm using a form in data entry mode to add new orders into a table. When the Save button is pressed, an append query posts the order number and date to another table, and then enables a subform for data in that table. The subform is linked to the main form by the order and date to get only the record that was just entered/created.

While the subform does display the desired record, the user cannot update other fields in that table through the subform. I've searched all over the forums, and I'm probably missing something really simple. I've tried turning data entry on and off, changing different subform properties but to no avail. I can filter out the proper record on the subform, I just can't update it! Any help would be much appreciated. Thanks!

View 2 Replies View Related

Update Combobox - Subform

Jun 20, 2005

I have a form called NewRequisitions that had a combobox in which the users select the Supplier (name, address, shipping details). I created a button to open a subform called NewSupplier so that they can add a new supplier to the Supplier table as needed. My code works okay for the first "NewSupplier" entry - the table and combobox are both updated and the new supplier is available for selection in the dropdown list. However, when there is a 2nd record that needs a NewSupplier added the combobox list doesn't get refreshed. The record is added into the table okay but you can only view it if the NewRequisitions (main) form is closed and then reopened.

Here is the code in my subform:

Private Sub Form_Close()

If CurrentProject.AllForms(NewRequisitions).IsLoaded Then

'save the record if there have been any changes
If Me.Dirty Then Me.Dirty = False

'requery combobox on other form
Forms!NewRequisitions!ComboSupplier.ReQuery

'yield to other events - and put the new list into use
DoEvents

End If

End Sub
-----------------------------------

Any assistance would be greatly appreciated!!!

View 7 Replies View Related

Subform Slow To Update

Jul 27, 2005

I wonder if anyone could offer some advice on a problem with a form & subform?

I have a main form, based on table PublicationOrders:
POrderID (pk)
EventID(fk)

And a subform, EventDisplay, based on table Events:
EventID (pk)
EventName

The main form and subform are linked on the field EventID and my master and child links are correct, as far as I can tell.

The field EventID on the main form is a combobox, bound to field EventID in the table PublicationOrders. It allows the user to select which event an order is being sent to. In the Afterupdate property of this combobox, I have the code: Forms!PublicationOrders!EventDisplay.Requery

I would like the subform to update to show the details of the event that has just been selected from the combobox. However, for some reason, on afterupdate, the subform displays whichever event was selected when the previous record was entered. If I close and reopen the form, it will right itself and show the correct event information that corresponds with the order.

Any ideas why it's doing this? I have searched here for a solution but can't seem to find anything that relates.

View 2 Replies View Related

Subform Search Update

Jul 27, 2005

Hi everyone. This probably sounds simple, but I cannot find the answer here, google, or my books (don't know if I'm searching the right words). I have a main form with five parmeter values, any of these acn be filled in, to filter the results in a query. I created a subform to display these results, however I cannot get the results to come up in the subform inside the main form. All I can get is the subform opening up in its own window, and even when this happens the subform inside the mainform stays the same. The only way I get the subform inside to update is by going into design mode and then back into formview mode. What have I missed or what am I doing wrong?

Thanks to anyone who takes time on this question.

View 6 Replies View Related

Update A Parent From The Subform

Oct 12, 2005

I currently have a subform that changes the image that is displayed when I click a button using

[student_picture].Picture = [student_image]

Is there anyway I could also update the image on a parent form, from the subform, something like

[ParentName.ImageNameOnParent].Picture = [student_image]

Thank You

Neil

View 5 Replies View Related

Problem With Update Subform

Feb 9, 2006

Hi

Hi got a good help with this and it's near a solution.
I attached a form that has checkboxes to show and hide fields.
This is a subform in a search form and bound to ID.
So if a person is checked for "eget initiativ" it sticks to him/her.
but the problem is that this should hide 2 textboxes, but if a person before has the bos checked for showing them, they doesn't hide when I filter the next person that has "eget initiativ check that's supposed to hide them.

If you uncheck it manually and check it again, they disapear. But that's not good enough.
Any suggestions?

View 2 Replies View Related

List/Subform Update

Mar 28, 2006

I have a private sub on a subform called txtsearchstring_change.

The txtsearchstring box is = to the client_id on the main form.

Basically when the user clicks on the next record button (which is on the main form) it should update the txtsearchstring_change on the subform and then display the new results in the list box.

Im not to sure how to do this. I understand im calling a sub from another form, so i did try a public not a private sub. And i have added on the next and previous button - txtsearchstring_change.

Thanks for any help.

View 2 Replies View Related

How To Update A Field In A Subform?

Jul 19, 2006

Hi!
I have Form1 with two subforms in. They are both on subform1 level (not nested).

From one of the subforms i can ask the parentform for a value in a textbox on parentform: Me.Parent!Controlname.Value

But:

How can i succeed updating a subforms textbox (called "textbox1"), when being on main (parent) form?

I have tried with this syntax: Me!Subform1.Form.textbox1.Value = "test"
Neither does: Me!Subform2.Form.textbox1.Value = "test" work..

Thanks for your help ;) I've spent hours with this one..

View 2 Replies View Related

Update Form From Subform

Sep 7, 2006

I have a form that has a related pop up from. I have an IF/Then/else statment. I want to up date a field in the orginal form from that if/then/else statement. I can get my IF/Then/Else statement to work on the subform but I can get it to update the orginal form.
I might have a problem with my forms![xxxx]![yyyyy] syntax.

Thanks for the help

View 1 Replies View Related

Update Subform With Combo Box...arg

Oct 18, 2006

This is so simple but I can't seem to make it work. I've poured through about 30 posts and tried all kinds of AfterUpdate event strings to no avail. Help!

Here is the simple setup. I have one table that lists employees and their corresponding teamleader. A grouped query provides the record source for a combo box. The idea here is that you select a teamleader from the combo box and a subform updates to show all the corresponding employees. This subform is based on a simple query of all fields on the table.

combo box = cboteamleader
mainform = frmTechMaintenance
subform = frmEmployees

I've finally settled on this...


Private Sub cboteamleader_AfterUpdate()

Me!frmEmployees.Requery

End Sub


...as having the greatest chance of success based on other posts. Not sure why it doesn't work. It just doesn't update.

View 6 Replies View Related

Query In Subform Wants To Update

Oct 26, 2006

After a scary recovery I have a new problem: Intially I changed the 'Data Entry' property on a subform, in a multi-tab form :eek: My tabs all dissappeared. (My Bad!:o )
Now that I have recovered I get a message when I close the form, asking do I want to save the changes to the design of the query.
This only seems to happen when I have read a second or third etc record that displays a different set of records in the subform.
This is a HR mdb with leave records in the subform.
Any help would be appreciated.
PS I thought I had posted this 2 hrs earlier, but can't seem to find it. Hoping this isn't a second posting ...:confused:

.... added an image of error

View 1 Replies View Related

Update Subform Using A Combo-box

Jan 19, 2005

Hi,
I am trying to update a subform by selecting a record in a combo-box that corresponds to a particular test when i select the said test i want an adjacent subform to change to the form displaying the test data in the subform window i have tried to use VBA to achieve this but so far have been unsucessful does anybody know how this can be done?

Regards
Gavin Cleary

View 1 Replies View Related

Combo Box Update Subform

Mar 23, 2006

I want to limit the values in a subform using a combo box. Basically I've got a Main Form that has name field etc. Selecting a different record shows me a different entry on the subform relating to a different name but now I want to use a combo box to limit what I see in the sub form another level. Any help would be wonderful.

View 1 Replies View Related

How Do I Update A Subform Based On A Query ?

Sep 14, 2006

I would like the subform in the lower left corner to be blank when
I open the program. It is populated from my tblClients table.
How it works in general is you select "x" number of items from the
list box...hit the search button and the results go through a query
and then show up in the subform.
everything works ok but, like I stated I would like the subform to
be blank when the program is first started up.
It seems to be retaining information from the last start up in the
subform box.

Thanks for your help...it's coming along great so far...
see attached doc...

View 4 Replies View Related

Won't Update: Subform Linked By Combo

Apr 6, 2005

I have a form that I have linked (master/child) to a combo box on my main form. I have a requery command for the subform set to the "After Update" event on the combo box. When the combo box is updated originally, the subform updates. However, if the combo box is updated after having a value, the subform does not update.

First off, is the requery command even the right command to use to update the subform? (it's source object is a table) Second, is there a reason it wouldn't work if that is the correct command?

Any ideas?

View 2 Replies View Related

Update Multiple Records In Subform

Aug 11, 2005

Hoping that this is a simple problem :confused:

I have an orders form and subform, ie one order can have several product records with the following firlds
Product
Ordered
Received
At the bottom I have a command button 'ReceiveOrder'
What I want is on click of this button that the received field (that is 0 by default) will equal Ordered.
ie If I had orderd 2 and I hit the button it would change the recieved to 2 as well.
This much I have done with the following code
Private Sub ReceiveButton_Click()
Me.frmReceivingSubform.Form!QtyReceived = Me.frmReceivingSubform.Form!QtyOrdered
End Sub

This work fine if there is only one product, but if I have more than one it will only update the selected record, is there an ammendment I can do that means all the product records on that particular order will update.

I've searched the forum for the answers but can't find the answer, can anyone help,
Thanks

View 6 Replies View Related

Main Form Update To Subform Value

Sep 2, 2005

hi

i have a main form, which has a subform (subform1)

subform1 has a further subfrom (subform2)

in the subform2, i have a value, which on a double click, i wish the main form critical value to refresh to

thereby the main form refreshes to the value of the subform 2 value

i cannot work out the code, you may have guessed from the description of the question, i am a bit of a novice.

could anybody assist, please

View 2 Replies View Related

Select From Combobox Then Subform Update

Nov 9, 2005

plz help me how to do it...

plz look at tables
i have "monthyear" & "partno/details and month" table

i cant open all part that have same month at table monthyear...
but i can open n view customer that bought same part at partno/details...

then i cant create form from table Monthyear..i want to create form as form "test"

plz help me...
am i need a coding?
can u show me the coding?
i'm new...plz..

ahh. cant post my DB....my db size to big...700kb after zip
cant i post screenshot? :confused:

View 1 Replies View Related

Losing The Plot With Subform Update!

Nov 16, 2005

right I have a subform which holds assessment data
it is a tab-control form which monthly assessment data each tab has its own form i.e. "frmCurrentAssessment-October2005", "frmCurrentAssessment-November2005" and they are based on their own queries i.e. "qryCurrentAssessment-November2005". It will not let me update the subform which is joined to frmAllStudents which is based on "qryallStudent"

the form and the subforms are joined by Admission Number. I can update directly from "qryCurrentAssessment-November2005". But the form wont let me update, I have already checked that the fields arent locked or that the form isnt uneditable and everything seems fine.

I am at a complete loss as to what to do here, is there something obvious that I havent checked

View 1 Replies View Related

Before Update For Subform In Datasheet View

Jul 19, 2006

I have a small issue with the before update event, it is triggered for every record of my subform in datasheet view rather than just once when the user leaves the subform.

I have a module which is called from my form and all the subforms. It tells the user that changes have been made to the record and gives them the option to undo. This is to prevent accidental editing when viewing records. This works fine, but sometimes the user must enter a number of records in a subform and so they must answer the message box every time.

Is there any relatively straightforward way to accomplish the warning and undo when the user leaves the subform rather than the individual record in it?

A quick attempt at lost focus and before exit demonstrated that these were not the way to go.

For reference, here is the before update code and the module

Option Compare Database
Option Explicit

Private Sub Form_Before Update(Cancel As Integer)
On Error GoTo Err_Handler

Call Confirm_Change

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Here

End Sub


Option Compare Database
Option Explicit

Public Sub Confirm_Change()
On Error GoTo Err_Handler

Dim Msg, Style, Title, Response

Beep

'Define message box
Msg = "You have made changes to this record. " & Chr(13) _
& Chr(13) & "Click 'Yes' to save changes " & Chr(13) _
& Chr(13) & "Click 'No' to undo changes "
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Record changed - Confirm save" '

'Check which button pressed
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MsgBox "Record has been updated with your changes", vbOKOnly, "Record saved"
Else
DoCmd.RunCommand acCmdUndo
End If

Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Here

End Sub

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved