Modules & VBA :: Tracking Form - Code Does Not Review All Subform Records
Jun 5, 2013
I'm having some problems with VBA code I'm writing that references a subform. I'm pretty new to VBA and am not sure what the problem could be. The parent form (TrackingForm) tracks dates different tasks were completed. The subform (Child2) tracks exceptions that also need to be marked completed. I need ALL dates to be marked as completed before the entire tracking record can be marked as completed.
I have the following code entered into the OnFocus event of the tracking records Me.Completed_Date field:
Private Sub Completed_Date_GotFocus()
If IsNull(Closing_Date) Or IsNull(Package_Received) _
Or IsNull(Upload_By) Or IsNull(Initial_Review_Date) _
Or IsNull(Me.Child2.Form![Date Exception Completed]) Then
Me.Completed_Date.Locked = True
MsgBox "Completed Date cannot be entered - outstanding items.", vbOKOnly, "Warning!"
Else: Me.Completed_Date.Locked = False
End If
End Sub
What ends up happening is that, if there is more than one exception record on Child2, the code only seems to care if one of the records (usually the first record) has something entered in the [Date Exception Completed] field. I can't seem to make it look at any successive records in Child2 to ensure they are also completed prior to unlocking the Me.Date_Completed field. I have googled this extensively over the last day and the only response that seems to recur involves using a Recordset function that I have never used and am unsure how to.
View Replies
ADVERTISEMENT
Feb 16, 2006
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load()
Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
If InputBox("What is the password?", "Password") = "2" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
End If
End If
End Sub
View 14 Replies
View Related
Jun 24, 2005
hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.
Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.
Help is appreciated.
Here is the code that I was thinking of, I don't think its right but it might be a start.
Dim Count As Variant
Dim CountImplementedTotal As Variant
CountCost = 0
CountImplementedTotal = 0
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordSet("ECM Details")
Do While Not rst.EOF
If rst![AUDIT ID] = Forms![audit info]![AUDIT ID] Then
If rst![Has Measure been Selected] = "YES" Then
Count = Count + rst![Total Measure Cost]
End If
End If
rst.MoveNext
Loop
Text47.Text = Count
View 3 Replies
View Related
Aug 4, 2015
The unbound Parent form has a listbox (SQL Server) , the selection updates the SubForm with a primary key ID. The Subform is a ReadOnly view from an Oracle Linked table. It populates with a Select * From Oracleview where ID = listboxID The code is at the link shown below update: The subform uses textboxes linked to the read-only record.
The main form can populate, no problem. However the SubForm data source is either populated OR is Null.
If it is Null, the subform stays blank. Two Objectives:
1. Set a boolean variable flag in the parent (main form) that subform record exist or that it is null. e.g. Parent obtain recordset count property from Sub form
2. Display the subform with blanks.
[URL]
View 3 Replies
View Related
Nov 11, 2013
Is there a way to review / edit the actual SharePoint workflows in SharePoint created by Access Services after a web database has been published?
View 2 Replies
View Related
Jun 12, 2014
I have a table which has duplicate records so I want to write down the code so that when the user click on a button then it should remove the duplicate records from the table.
View 2 Replies
View Related
Oct 21, 2014
How to give a VBA code for duplicate records is without entry in Subform datasheet. I just used in the screenshot code but that is not working properly. how to change a vba code for without entry duplicate names.
View 11 Replies
View Related
Jul 18, 2006
I need to be able to remove the Link Child field and the Link Master field from a subform when I click on a command button on the main form. Is there code that will let me remove these links so that I can add a new record to the subform?
View 1 Replies
View Related
Mar 29, 2014
How to track deleted records? I have a database that users login to and it tracks which records they make changes to but I do not have a way to track a deleted record. I read online about doing like an archive of the record to a table that will hold the deleted record and that made the most sense to me.
View 3 Replies
View Related
Jul 9, 2014
I have 12,000 cvs that i need to get into An access database so i can start to extract email info etc. They are stored in one folder All Cvs under each of their names edc.txt (i have converted them to .txt) i want to create one table with two fields name (taken from the cv filename and contents (taken fromthe contents of the .txt file). I am using Access 2007.
View 2 Replies
View Related
Jul 24, 2013
I am working on a project where I am creating a database that tracks open orders (old and new orders that still need to be billed but haven't been billed for one reason or another).
I have my "Master" table built and now I need to create 2 queries, one query retrieves the Top 20 orders based on value and that are older then 30 days and the second query is to retrieve the Top 100 orders based on the same criteria but the Top 100 query should not include the orders that appear in the Top 20 query.
My Top 20 query is below
SELECT TOP 20 MASTER.COMBO, MASTER.[Customer Name], MASTER.Status, MASTER.CCD, MASTER.Area, MASTER.[Order Type], MASTER.[Order Value], MASTER.AGE, MASTER.ANALYST, MASTER.COMMENTS
FROM MASTER
WHERE (((MASTER.AGE)>=30))
ORDER BY MASTER.[Order Value] DESC , MASTER.AGE;
I have tried multiple ways to do the Top 100 query and exclude what is returned in my Top 20 query but so far no success.
View 10 Replies
View Related
Mar 11, 2015
where I can find VBA for (Tracking User LogIn/LogOut) I have tried researching to no avail.I just need a simple code to track users logging date & time in and out of my DB.When they click on the icon on the desktop id opens the DB to login screen with username & password then into the DB.
View 9 Replies
View Related
Jun 16, 2014
I have a form which has a subform that holds records from a table. When in the table i can move through the records with my up and down arrows but when I am in the subform i can not move in the same manner with up and down arrows. What needs to be done on subform to allow user to move through records using up and down arrows.
View 1 Replies
View Related
Mar 5, 2015
I have a form with a subform. I would like a control in my parent form to uncheck a checkbox control in my subform when that field value is deleted . I can get that to work if there is only one record in my subform but it doesn't uncheck the rest. how to I reference all the records in my subform so all of them will uncheck when I delete that field value?
View 2 Replies
View Related
Nov 16, 2013
I have created three forms using three tables tblUser, tlbMeasure and tblSubMeasure. The tblUser is linked to the tblMeasure table via UserLogin and tblMeasure is linked to tblSubMeasure via MeasureID. The forms are created with frmMainMeasure being the main form and frmMeasure6 a subform and frmSubMeasure a sub with frmMeasure6.
I want to be able to add sub measures that will be linked to each MeasureID and each MeasureID link to each user via UserloginID. The forms load the data in the sub forms but when I try to add sub measures using a button I added on the frmMeasures6. The button works when I try work on the form fine but when I try via the main form I am not able to add or view other records within the table.
When I click the Add button I want to be able to load back the same MeasureID number in fmrSubMeasure to ensure that it is linked to the same measure and the MeasureID is linked to the same UsrrLoginID.
The code is included below and I've attached the file as well.
Private Sub cmdAdd_Click()
If Me!frmSubMeasure.Form.Dirty = False Then
End If
Me!frmSubMeasure.SetFocus
DoCmd.GoToRecord , , acNewRec
[Code] ....
View 1 Replies
View Related
Sep 9, 2013
I have button the when pressed will insert a record (via an insert into query) to the frmMain and several records to the frmSub.The problem is when a new record is created in the main form the auto number primary key advances. This is fine however the linked Child field which is a foreign key of the main form is not added and the value is blank. I thought this would happen automatically and the Master field would force the child field to be update but this is not happening.Do I have to determine the max number used in the auto number then add 1 to it?
View 7 Replies
View Related
Feb 23, 2015
I am trying to add a save/close button to a form that will only close if certain conditions are met and if they arnt then display a message box.I got the basics on how to save and close the form, do a simple if statement and display the message I want but the problem im running into is my subform can have multiple records and of those records each records status is set by 3 different checkboxes. I need all of those records status to be same when the form is closed.
if I do an if statement like
If Me!frmDataform1subform.Form!StatusID.Value = 1 then
docmd....
then all it looks at is the first record, how do I get my if statement to look at all the records in the subform?
View 2 Replies
View Related
Sep 12, 2013
i want to Count recrods by a pressing a button from a subform.
After counting the records from the subform, it should compare the amount of the records with a number.
The user can put in the number.
The field for the number is called number.
The button is in the form.
I've created the following code.
The Name of the subform ist Abfrage_Vergutungssatze_Unterformular
Code:
If DCount("*", Me.Abfrage_Vergutungssazte_Unterformular) > Me.number + 1 Then
MsgBox "Please recheck!"
I've get an error.
error message: errors when compiling
View 4 Replies
View Related
Jan 20, 2014
i have being requested to create a from that displays the number of files in different folders. I can accomplish this somewhat with the code below. In the example below it is only showing the code for the first 3 folders. I have 7 folders in total to count.
1.Each week the location of the data will change. In the example below it is showing 2014-W03, the following week it will be 2014-W04 etc. etc. I was thinking of having an input box in the form that the user type the date into, to trigger the code, but how would I go about changing the code to include this variable?
2.I need to repeat this code for 9 other products, so my form will be displaying data for 10 products in total. I know I could copy this code 9 times and modify it accordingly, but is it possible to loop it in some way?
Code:
' Returns the total number of fails
Private Sub Form_Load()
Dim file As Object
Dim fileCount As Long
With CreateObject("Scripting.FileSystemObject").getfolder(" estmachineProduct1 FAILS2014-W03")
For Each file In .Files
[code].....
View 11 Replies
View Related
Apr 17, 2014
I have two tables, One table containes customer name, etc., tblcustomers the other table contains the tbltimelog, log of activity start time end time. No issues. I want to generate a form based on the dateofentry for the time record. I am not able to get a summary lets say I spent 20 hours on ABC and 20 hours on CBS. If I spent 3 different days on ABC and 6 different days on CBS it will not add them together it will list it ABC 3 times and CBS 6 times. I have tried the distinct function but it doesn't work because the date is distinct. Is there a way to do this as an SQL or whatever. Thinking I could just create a new table and calculating the totals but that seems to be a waste.
View 3 Replies
View Related
May 26, 2015
I am working with a database that deals with trailers. What is happening is a salesman takes an order for a make and model for a trailer for a customer. The customer can then add some customization to the trailer such as more tail lights or tie down straps. They add the customization on a form called frmCustomQuote. On frmCustomQuote there is a subform called sfQuoteConfigs. On sfQuoteConfigs there is fields such as category and sub catergory that get populated bases on what was selected.
On frmCustomQuote there is a button called "Copy Quote" this will allow the salesman to make an exact copy of the trailer and customization. The quotes are held in a table called tblQuotes and the customization is held in tbQuoteConfigs. Now my problem is when I click on Copy Quote it only copies the first record into tbQuoteConfigs. I can't figure out a way to tell my code to move to the next record within sfQuoteConfigs.
My code:
Code:
Option Compare Database
Option Explicit
'Setup ADODB connection to the tblQuotes
Dim adoQuotesCustomQuote As New ADODB.Recordset
'setup ADODB connection to the tbQuoteConfigs
Dim adoQuoteConfigsCustom As New ADODB.Recordset
'Dim the Variables
[code]....
View 4 Replies
View Related
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
Jun 3, 2014
I have got a received form that has 2 subforms.
The main form is based on Received Table
The 1st subform is based on a query that shows the order detail and how many units are outstanding.
The 2nd subform is based on a receivedDetail table. so will have a list of items with the original order qty, and the qty still outstanding.
When the main form loads i want it to create new records into the 2nd subform based on the 1st Subform.
so the 1st and 2nd subform run parallel with each other and appear to be as one to the user.
both subforms are continuous style.
Code:
Private Sub Form_Load()
With Me.Form.RecordsetClone
Do While Not .EOF
.AddNew
!UserFK = Forms!frmReceive!cbxUsername
[Code] ....
View 3 Replies
View Related
May 23, 2014
Im v new to VBA and been having trouble getting the following code to loop through the records in my subform:
Private Sub cmdComplete_Call_Click()
Dim rs As dao.Recordset
Set rs = Me.fsub_Call_Off_Quantities.Form.RecordsetClone
rs.MoveFirst
[code]....
It only seems to update the first records txtQuantity_Called_Off value. I have tried all the 'looping' code variations i can find and they all seem to only update the first .
View 6 Replies
View Related
Feb 26, 2015
I'm working with a form someone else created and am adding a delete button. I've managed to add the button and get some code working which will delete a record from a table. The problem I'm having is that after the delete operation occurs all fields in the form, and the corresponding items below the form, show #Deleted in all fields. How can I force a refresh using code?
View 11 Replies
View Related
Sep 5, 2014
So I have a form which is showing the current record and you can scroll through these and make modifications to them from the form as opposed to using a table.
The form has the following fields populated from the main table
Customer name (Can appear more than once)
Status
Date
Servers
Positions
In addition it has several buttons for next record, previous record, first & last record, new record, delete record and update record.On this form i have a text box that i want to be able to use to search for a record using the customer name when you press the search button associated with it. I've got some code and it is mostly working but it seems to be moving the text entry cursor to the date box of the record for some reason.
What I want it to do is take the text from the search box and find it and move the form to that record.
Code:
Private Sub btn_cstmr_srch_DblClick(Cancel As Integer)
On Error GoTo HandleError
Dim strFindWhat As String
strFindWhat = Me.txt_cust_search.Value
[code]....
View 6 Replies
View Related