Modules & VBA :: Subform Checkbox Used As Condition To Update Records
Aug 26, 2013
My subform consists of a list of tasks that are waiting to be verified. in order to verify tasks, the user scrolls through the list of tasks and checks a checkbox (discrepancyverified) on each record they wish to verify. After the user has finished checking all the records they wish to verify, they click a verify button on the main form which should then go back through each record and update the verifieddate value of any that are checked to today.
This is what I have so far:
Code:
Private Sub Command19_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
[code]...
View Replies
ADVERTISEMENT
Aug 13, 2014
I have a form that contains a subform. I want to make a button that on click updates all the records listed in the subform. This is the best I came up with.
Dim rs As DAO.Recordset
Set rs = Me.SubList_for_Billing_Center_Form.Form.RecordsetC lone
With rs
.MoveFirst
Do While Not .EOF
.Edit
UPDATE Billing SET Billing.Billing_Declined = True, Billing.Billing_Declined_Date = Date()
[Code] ....
View 1 Replies
View Related
Feb 12, 2007
Dear all:
I have an access database with about 200 records. Names, ID's, addresses, a checkbox called "apply_to_all" and a date field which is selected via a combobox.This combo box is called "date_started", and a textbox called "prefix". This textbox has a default value of "Given on".
What I am trying to accomplish is: A date is selected from the combobox, then a checkbox("apply_date_to_all") is checked and this date is applied to ALL records in the database along with the default value that is in the prefix textbox. (Concantenattion?)
I assume this can be accomplished by some soft of sql statement?
An help is greatly appreciated.
thanking in advance,
Dion
View 1 Replies
View Related
Mar 8, 2006
i'm making form from query. but all i need to do is calculate days worked (TimeService) either worker still working or resigned.
i want to know is it possible to make it condition like it to query? if not can u tell me the other way?
View 2 Replies
View Related
Jun 21, 2005
I have a form with a subform inside of it. On the subform I have created a field for a check box. I wish for the user to be able to click 1 or more check boxes in the subform and then click a delete button that removes all selected entries.
I have this code from a seperate post regarding this problem, but am not sure how to edit it to reflect what I need.
Sub DeleteRecordButton_Click()
'Delete the selected record
dim strSQL as String
strSQL = "DELETE * FROM mytable WHERE mytblID = " & Me![mySubForm].Form![mytblID]
DoCmd.RunSQL strSQL
MsgBox("Confirmed")
End Sub
Thank you!
View 1 Replies
View Related
May 25, 2014
I have the following module which displays the backcolor of a checkbox label if it is true or false
Private Sub FormatLabel(chk As Control)
With chk.Controls(0)
If chk Then
.BackColor = vbYellow
.BackStyle = 1
.ForeColor = vbRed
[Code] .....
Which works great! But I cannot figure out how to make it work in the control AfterUpdate Event. I tried using it in the current control's (checkbox) after update event
Dim itm As Control
If itm.ControlType = 106 Then FormatLabel itm
but nothing happens (no change, no error message).
I have tried creating a new module:
Public Sub CheckBoxFormat(chk As Control)
If chk = -1 Then
chk.BackColor = vbYellow
chk.BackStyle = 1
chk.ForeColor = vbRed
Else
chk.BackColor = vbWhite
chk.ForeColor = vbBlack
End If
End Sub
But when I try to call it in the AfterUpdate event for the particular checkbox
Private Sub CheckBoxA_AfterUpdate()
Dim itm As Control
If itm.ControlType = 106 Then
CheckBoxFormat itm
End Sub
I get the error message "Expected Variable or Procedure not module"
So, (1) is my module all wrong or (2) am I calling it incorrectly or (3) wrong on both items?
View 13 Replies
View Related
Aug 2, 2014
In an access form, I have several checkboxes. There is one checkbox titled "Complete". The complete checkbox needs to be true only if all other "non master" checkboxes are true. If all of the non master checkboxes are not checked, I need the complete checkbox to be false. This is the code that I am currently using on the after update command:
If me.checkbox1 and Me.checkbox2=True then
me.completed=True
else
me.completed=False
end if
This works fine if there is no "and" in the if statement and the condition is based on the status of one checkbox as opposed to many (Also I have about 15 checkboxes that must be checked before the "complete" checkbox is true).
View 14 Replies
View Related
Jul 23, 2015
I have a parent form which has a yes/no checkbox in the form's record source. Then in that parent form I have a sub-form. If NO records exist in the sub-form I want the checkbox to be UN checked. If records DO exist in the sub-form, I want the checkbox to be CHECKED.
But I want this to happen as records are added or deleted from the sub-form. In other words, if the parent form is opened and no records exist in the subform then the checkbox should be unchecked. But as soon as the first record has been entered in the subform, the checkbox on the parent form should be checked. Likewise, as soon as the last record has been revoved from the sub-form, the checkbox on the parent form should be unchecked.
What code do I need to accomplish all of this?
View 1 Replies
View Related
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
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
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
Oct 31, 2006
Hi, im very new to visual basic programming...what i am trying to do is implement an If Statment on AfterUpdate for my form:
If record_is_new Then
Dim db As DAO.Database
Dim RS As DAO.Recordset
Set db = CurrentDb()
Set RS = db.OpenRecordset("LTOT_second_oxygen", dbOpenDynaset)
RS.AddNew
RS![HospitalNumber] = Me![general_info.HospitalNumber]
RS.Update
RS.Close
db.Close
Set RS = Nothing
Set db = Nothing
.........more of this kind of code
Else nothing
End If
any ideas on the vb code that would do this?
View 5 Replies
View Related
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
Jan 25, 2015
I have a calculated field in the form footer which adds up the number of boxes that have been ticked for the received field
=Sum(IIf([Recieved]=Yes,1,0))
If the ticks equal to 3 then I want to update the status field in another table to "Active".I am trying this VBA code but it won't work.
Code:
If Text9 = "3" And custNumber = tblCustomers.custNumber Then
tblCustomers.Status = "Active"
View 3 Replies
View Related
Aug 7, 2015
I want to update a table called tblFinalOrder, that looks like this:
In particular, I want to update each column separately with the number 1 taken from table tblSAP_XWP_SW:
My problem is that Access doesn't properly update the table how I want. I join both tables using an INNER JOIN on the SAP number. In the column AEMenge you see some 1's. So what he should do is writing these 1's into the appropriate column in my tblFinalOrder table. The condition is that the SAP number must already be in my tblSAP_XWP_SW table. If he doesn't find a 1, then skip it.
Here is my code so far. This one should update column "DynaCT". The funny thing is that DynaCT isn't available in my columnlabel column but he writes a 1 anyways (or in this case anohter number, I was playing around with it). This is what I don't understand.
Code:
UPDATE tblFinalOrder AS a
INNER JOIN tblSAP_XWP_SW AS b
ON a.SAPNr = b.sapxwpsw_sapnr
SET a.DynaCT = 1
WHERE a.SAPNR IN (SELECT sapxwpsw_sapnr FROM tblSAP_XWP_SW);
I mean, he should only write the 1 into the specific column of table tblFinalOrder, if he finds the SAP number in tblSAP_XWP_SW and if there is a 1 in this line in column AEMenge.
View 3 Replies
View Related
Jun 21, 2013
I would like to update the SQL for a subform. I have an SQL statement that I have placed into a string and now I would like to update the subform using it's RowSource property, but I get an error when trying to use the following statement:
Forms![frmModelSales].[frmModelSales-Subform-
QuestionableShip].RowSource = strSqlForSummary
(the message board has split this into two lines, it is really one)
View 3 Replies
View Related
Jan 24, 2006
I have 2 combo boxes which I select a 'Year' and an 'Incident'. Based on 'incident', a listbox lists whether it was a 'bad' or 'good' incident.
I only want my subform to pull up the record if the listbox says 'bad'. how do I do that?
View 3 Replies
View Related
Feb 27, 2015
I have an access 2010 database with an initial form for user login. After selecting their login, users go to the main tabbed navigation form. On the first tab, I have form with a dashboard as a subform containing a field that says "Welcome" with the user's name (obtained from the login). My problem is, users have to click on the subform for the name field to update. How to have the name automatically populate when the login form closes.
View 2 Replies
View Related
Jul 23, 2015
I have a main form and 2 subform. The first subform has a field for %. And the 2nd subform has series of checkbox (Checklist or error made by the student)
Because every checkbox will lower the mark from 100 to x points per checkbox.So, I want that, if the field is not set to 100 yet, then they will not be able to check any box in the subform.I tried, before update of the subform, etc, but none work properly.
View 4 Replies
View Related
Aug 4, 2015
As the unbound parent list box record selection is changed, I want the sub form to refresh. Do I put code in the Subform Current Record event?
Master Form Name: frmsr_NewWellEntry
Unbound Listbox - when record selected the primary key populates txtNavWellID (unbound) on parent form
subform Container fsubsrNavSHLBHL Link Master Fields =Forms![frmsr_NewWellEntry].[txtNavWellID]
The read-only form in the subform Record Source is something like: select * from vsrNavigatorSHLBHL where Well_ID =90243..Key The Form used as the subform above will be re-used in multiple parent forms. The parent form data is form SQL Server, the subform from Oracle.
View 4 Replies
View Related
Oct 16, 2014
I have a table with about 1,2million records. I get an Excel list of about 35000 weekly, using which the table has to be updated: the records, that exist get fields updated, the ones that are new have to be added.
What is the fastest way to do this?
I'm trying with this code, but it's awfully slow, even if I read the update ranges into memory:
Code:
Set cn = CurrentProject.Connection
Set Rs = New ADODB.Recordset
Rs.Open "Select * from tbl_TTextract", cn, adOpenDynamic, adLockOptimistic
i = 2
Do While i < lr_2 + 1 'update
With Rs
[code]....
View 14 Replies
View Related
Oct 31, 2013
I am working on a database project where I will Select a record on a subform
See the picture attached.
I know that using DAO recordset, i can update the checkbox one by one, but it become way too long when I have thousands of record...
May I know if there is a way to Select entire column of the subform?
View 1 Replies
View Related
Mar 18, 2015
I have a form and a subform
Which method should I use if I need to update automaticaly a field in form based on an event in subform ?
Just to be more clear, have a look in the below example.
In first print screen, student "Johnson Nick" has not completed all tests in TestA subform, the form field "DateCompleteA" is blanc.
Once a student completes all tests in Period A, I need the form field DateCompleteA to show the date that he completed the last one. (18/3/2015).
If student has not yet completed all tests in TestA subform the Form field DateCompleteA must be blanc.
Student has not completed "literature"yet, DateCompletA field is blanc
Student completed ALL his test, subform is updated, Form field DateCompletA is updated with the maximum date.
View 3 Replies
View Related
Dec 27, 2013
I am new to MS Access and using MS Access 2013.I just added 50 new fields into a table. I need to update these new fields on every record.The table layout is as followed:
Table1
-Company Id
-Question1
-Question2
-Question50
[code]....
View 6 Replies
View Related
Mar 5, 2015
I have a datasheet subform where i need to select some records by clicking a check box and update a certain field/s of only those selected records.
View 1 Replies
View Related
Dec 8, 2012
I have a quotation database running in our company network which we use to send quotes to customers. A quote may have one or more items in it and each item has different prices based on the quantity. Each item will have a record in the database. So, if a quote has five items, there will be five records with same quote number and if 30 items, 30 records with same quote number.
Many times, same enquiries are coming from different sources (customers) asking quotes specially Govt orders. Now we need to take every enquiry as separate and feed separately in the database. Consider the time for a quote which has 30 items in it and each items has 6 level of pricing. When this same enquiry is coming from 12 different customers, it takes a lot of time to feed it in.
Now my question: How can I copy all the records belongs to one "quote no" and add it to the same table with a different quote number and some changes to the other customer related fields? If I can do that, I just need to change the customer address and I am ready with the second, then third and so on. I want to copy all related records, change quote number and related fields and then append to database.
Also I need this when I revise a quote. The system needs to keep the old quote as well as the revised quote for future reference. If I can duplicate it in one command, I just need to do the changes, Revision Number, Revision Date etc. and the revision is ready to go. Now I use update querries to do changes to all related records during revision. Then I will have only the revised quote in my database and when I want to refer the old quote, I have to refer the hard copy.
I know there is a "Duplicate Record" command available in form level (through wizard), but that duplicates only one record, not a selection of records as I need it here. If this can do with macros, fine. I am not an expert in VB, but if I get the code, I will attach it to a command button and use.
View 1 Replies
View Related
