Modules & VBA :: Checking Multiple Subform For Values Before Closing

Jan 29, 2014

I am looking to check 3 sub forms for values and if they contain any then delete them. After a bit of googling I discovered that you should use recordset.recordcount - to which I tried but I get method or data not found - is this a valid method?

My code to which Im using is:

If Me.NoneChargeable_Admin_subform.Recordset.RecordCount >= 1 And Me.NoneChargeable_Manufact_subform.Recordset.RecordCount >= 1 And Me.NoneChargeable_Research_subform.Recordset.RecordCount >= 1 Then
DoCmd.RunSQL "DELETE NoneChargeable_Admin.*, NoneChargeable_Manufact.*, NoneChargeable_Research.* " & vbCrLf & _
"FROM NoneChargeable_Admin, NoneChargeable_Manufact, NoneChargeable_Research;"
End If
DoCmd.Close acForm, "NoneChargeableHrs_frm", acSaveNo
End Sub

View Replies


ADVERTISEMENT

Modules & VBA :: Checking Null Values In A Field

Jul 10, 2014

The following code doesn't check null condition in Scannedby field. The scannedby has text data type. The code gives me all the data where scandate=20130722 but doesn't check that scanned by can't be blank.

Code:
rs.Open "select count(*) from BatchTBL2 where scandate=20130722 and scannedby <> null", cn, adOpenKeyset, adLockOptimistic
j = rs.fields(0).Value

View 5 Replies View Related

Modules & VBA :: Checking Fields In Multiple Tabs?

Aug 16, 2013

I have a form with mandatory fields highlighted a different colour (yellow or blue). On the form are 3 tab pages with subforms which also have these fields.

I have added a checkbox named incomplete to each tab page and to the form. My intention is to try to write some code to look at each field on the page and if the non-white (ie mandatory) fields all have a value, then the incomplete on that page is changed to no. The code then checks the incomplete value for all 3 pages - if these are all no and the form's mandatory fields are also filled in, then this also becomes no.

When creating reports, I can then find out which records have not been completed and notify the relevant staff. Also if the data is incomplete, they don't want those records appearing in reports - so I can use the incomplete value from the form.

View 4 Replies View Related

Modules & VBA :: Duplicate Checking - Multiple Criteria

Jun 15, 2013

I'm using Access 2013. I want to check duplicate data. I have a form where from insert data into table but before insertion I want check multiple criteria with form. How can I do this

Field are:
BatchID
BillNum
CIH
IH

back end form code is:

Private Sub cmdCheck_Click()
Dim criteria As String
criteria = "[BatchID]=" & Me.cboBatchID & " AND [BillNum]=" & Me.txtBillNum & " AND [CIH]=" & Me.txtCIH & " AND [IH]=" & Me.txtIH & ""

[Code] ....

Finally I'm getting this error: Run Time Error 3075 Syntax Error Missing Operator in qry Expr.

View 3 Replies View Related

Forms :: Checking For A Filtered Duplicate Value On A Form Before Closing

Apr 11, 2014

Below is some code that I'm struggling with and I get an error message "Type Mismatch".

Description: The database contains multiple projects and the subset of each project is a release. The "PublishedNumber" can't be a duplicate within a release, but it can be a duplicate within the table where the data is stored. There can however be multiple occurrences of 0.

Here's my code:

Dim stLinkCriteriaRelease As String
Dim stLinkPublishedNumber As String
Dim DuplicateNumber As Variant

stLinkCriteriaRelease = "[AssociatedRelease]=" & [AssociatedRelease]
stLinkPublishedNumber = "[PublishedNumber]=" & [PublishedNumber]

[Code] ....

View 3 Replies View Related

Modules & VBA :: Checking Multiple Criteria Search Code Is Correct?

Feb 25, 2015

Staff are monitored to make sure they are keeping up to date with our customers. A customer can have multiple projects going through the factory at any one time. Each customer has a record per project and a 'general' record. Ideally we would like our staff to be able to move the 'general' record when they update a project record as opposed to either having to find and then update the general record after, or forgetting and calling the customer again 2 days later!

Including a msgbox for the EnqNum seems to show the general record correctly, however being new to access I am unsure if I have the update part correct.

Code:
If Me.chkMoveGen.Value = "-1" Then
Dim EnqNum As Integer
EnqNum = DLookup("[e_id]", "tblEnquiries", "[c_id]=" & Me.txtc_id & " and [e_status] = " & "13")
DoCmd.RunSQL "UPDATE tblEnquiries " & _
" SET e_date_due=#" & Format(Me.txte_date_due, "MM/DD/YYYY") & "#" & _
" WHERE e_id= EnqNum"

View 3 Replies View Related

Modules & VBA :: Checking Checkbox (from Multiple Checkboxes) Based On Field Value

Nov 29, 2013

I have a field 'Payment Types' with values (Cash, Cheque, Debit/Credit Card) and a field 'Payment Received' which is Yes/No.

When putting the order through the user selects the payment type and ticks a box if payment has been received.

On a report for delivery drivers, the owner wants it simple for the driver... he wants all the payment types listed with a checkbox next to each one, then wants the appropriate box ticked if payment has been received.

So I need something on the report (or underlying query) which ticks the appropriate box, i.e.

If payment received = true then payment type checkbox = true.

Or should I put the payments into a separate table with both fields so multiple payment types can be marked as paid?

View 6 Replies View Related

Checking Values Of Textbox With Table Values

Sep 15, 2005

There is a text box in a form that user can enter the value.
How can I check if the value entered by user is already existed in the record of the linked table or not?
If the value (or record) do not exist, that new value/record will be added to
the table.
If the value do exist, just show the msgbox to indicate.

How can I do that ? :confused:

Thanks

View 1 Replies View Related

Modules & VBA :: Passing All Values From Subform To Another Subform

May 26, 2015

I know how to use openargs to pass values between forms but im not sure how to go about passing all the values from one subform to another.

To be specific I have an order form (frmCustomerOrders) that has fields like CustomerName, EmployeeName, OrderDate and so forth, the subform (frmCustomerOrdersSubform) contains information on the Products the customer is ordering which usually has multiple records with fields like ProductName, PartNumber, Quantity and ItemNumber. I want to pass the values from the CustomerOrdersSubform form to another subform (TransferSubform) My initial thought was to use something like the openform command through openargs but im not sure if that will work or if there is another method I should use.

View 9 Replies View Related

Forms :: Count Multiple Values In Subform

Feb 4, 2014

I have a form and subform. I need to -

1) show the number of records in the subform on the main form
2) count the number of records in the subform where a value [Public] is True
3) count the number of records in the subform where another value [Analyst] is True.

I can achieve the first two by using the following VBA on the Main form current event -

Quote:

Private Sub Form_Current()
Dim rst As DAO.Recordset
Dim lngCount As Long

[Code]....

when i try to get number 3 done I get the same value as for [Public] (using lngCount for both...not surprising really!)

how i can get a count done for [Analyst] = True in the same event?

View 3 Replies View Related

Checking For Duplicate Values

May 10, 2005

Hi,

I have a form in a database for our products (doors). I have a button that duplicates one of these records (all new products are based on standard template records).

When this record is created a new product number is produced by the system which was the number of the original (template) record plus the customer enquiry number.

I want to be able to warn the user if they are trying to save this newly created record with a product number that already exists so that they can alter the number to be unique. It will also warn them if they re-number an existing record to another already existing product number. Since the field is defined as unique, at present the system says absolutely nothing and just doesn't save the newly created record which seems is a touch annoying!

I have tried several things with dlookup, dcount arguments but can't seem to get anything to work.

Hopefully someone here can point me in the right direction?

Thanks in advance

John

View 2 Replies View Related

Forms :: Checking A Value On Subform?

Jul 25, 2014

I am trying to verify that there is no value entered in a text box on a subform before the main form closes. I have tried all manner of combination but continually get the statement that Access can't find the field referred to in your expression. I do have the names of all forms and fields correct.

Below are a couple of what I have tried. (SubassemblyFRM is the main form and ItemsSFRM is the subform and Description is the text field I am trying to check.)

Forms![SubassemblyFRM]![ItemsSFRM].Form.Description

Me![SubassembliesSFRM].Form.Description

I am trying to use it in this statement.

If Len(Me![SubassembliesSFRM].Form.Description & vbNullString) = 0 Then

what might be causing this error?

View 1 Replies View Related

Modules & VBA :: Create Values From Other Subform?

Nov 6, 2013

my parent form has two subforms. when the user double clicks a value on one subform it populates the first blank field in the other subform. I created a code for the double click on the subform

Code:
Forms!frmeventbuild!Form.frmBUILDsub.EMPLOYEE.Value = Me.FULL_NAME

View 2 Replies View Related

Checking Multiple Columns + Asp

Oct 16, 2006

Question concerning how to check a value against multiple columns in Access with ASP.

Need help converting this to proper ASP and Access syntax

Code:myId = "test"myName = "monkey"SELECT * FROM table1 WHERE (programId1='" & myId & "' OR programId2='" & myId & "') AND (name1='" & myName & "' or name2='" & myName & "')"

is this doable with asp and Access?

If this helps explain it better, the following is how I would call it with PHP & mysql:

Code:$myId = "test";$myName = "monkey";SELECT * FROM table1 WHERE (programId1 = $myId OR programId2 = $myId) AND (name1=$myName OR name2=$myName)

Any help would be appreciated

View 2 Replies View Related

Checking Multiple Check Boxes At Once

Mar 10, 2005

Hi there,

I have several check boxes, that are however categorised, in my form. I was now wondering, in order to save some time, how could I make a check box that marks/unmarks all the boxes under that specific category ??

Cheers,

Pekka

View 1 Replies View Related

Checking For Null In Multiple Fields

Oct 19, 2004

If I have about 9 Text fields, How would I go about checking to see if all of the text fields are empty, then hide all the fields, but if any of them have anything in it, show them all. Here is my code, but it doesn't work:
Code:If SpecAgent = "" And SpecArea = "" And SpecBenefit = "" And SpecCompany = "" And SpecCSR = "" And SpecDoctor = "" And SpecHospital = "" And SpecPlan = "" And SpecRx = "" ThentglNo = TruetglYes = FalselblSpecAgent.Visible = FalseSpecAgent.Visible = FalselblSpecArea.Visible = FalseSpecArea.Visible = FalselblSpecBenefit.Visible = FalseSpecBenefit.Visible = FalselblSpecCompany.Visible = FalseSpecCompany.Visible = FalselblSpecCSR.Visible = FalseSpecCSR.Visible = FalselblSpecDoctor.Visible = FalseSpecDoctor.Visible = FalselblSpecHospital.Visible = FalseSpecHospital.Visible = FalselblSpecPlan.Visible = FalseSpecPlan.Visible = FalselblSpecRx.Visible = FalseSpecRx.Visible = FalseElsetglNo = FalsetglYes = TruelblSpecAgent.Visible = TrueSpecAgent.Visible = TruelblSpecArea.Visible = TrueSpecArea.Visible = TruelblSpecBenefit.Visible = TrueSpecBenefit.Visible = TruelblSpecCompany.Visible = TrueSpecCompany.Visible = TruelblSpecCSR.Visible = TrueSpecCSR.Visible = TruelblSpecDoctor.Visible = TrueSpecDoctor.Visible = TruelblSpecHospital.Visible = TrueSpecHospital.Visible = TruelblSpecPlan.Visible = TrueSpecPlan.Visible = TruelblSpecRx.Visible = TrueSpecRx.Visible = TrueEnd IfEnd Sub

View 2 Replies View Related

Modules & VBA :: Parsing Multiple Values From A Textbox

Jun 5, 2014

I have a form in which the user has the privilege to download some data from the table.the user will paste some of the numbers to the textbox from the excel in order to download the specific data.i need to write a query to parse the textbox using the in condition

View 12 Replies View Related

Modules & VBA :: Check If Multiple Values Exist?

Jun 17, 2014

I am making an asset table which amongst other fields have fields for "serialNo" and "Manufacturer"

I am trying to write some code that after update of manufacturer in the form, will check to see if that serialNo and Manufacturer exist.

ive managed to do it for one value, using

If Not IsNull(DLookup("[serialno]", "Assets", "[serialno] = '" & Me!serialno & "'")) Then
msgbox "blah blah"

which works great, but am struggling when i'm asking it to lookup two values.

View 9 Replies View Related

Closing A Subform

Feb 14, 2005

Hello,
I need to close a subform to update the controls on it (deleting and adding), and without closing the main form; without using the "visible" propriety.

Thanks

View 2 Replies View Related

Modules & VBA :: Selecting Multiple Values From List In A Form

Dec 29, 2013

I am trying to use a list-control on a form to let the user select multiple values. I have understood that this requires some VBA-code to step through the selections in the list, since the "multivalue-selection" is set to "Extended".

When I try to execute the code I have (found and have tried to adjust), then I get the error message "Object required". The "ListCount"-paramater always only results in a ZERO-value, when i step through the code:

Function cmdOpenQuery_Click()
On Error GoTo Err_cmdOpenQuery_Click
Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim i As Integer
Dim strSQL As String

[code]....

View 9 Replies View Related

Modules & VBA :: How Can Function Return Multiple Values And Not Re-run In Query

Jan 11, 2014

Trying to run a query where each 4 fields calling a custom function will not just re-run the same custom function over and over again for each field in a single record.

A Function has a huge amount of multiple queries and logic to perform.The Function returns a Integer, Integer, Integer, and optional Integer. Each integer requires a DLookup to lookup a String description value for each individual integer (in each of 4 fields).

The problem is, the DLookup in each column that runs against each of the integers re-run the same function.The result is that a single record, each of the 4 columns returning a single of the 4 values, the complex function is re-run 4 times.

The function is huge, part of a Business Rules Engine. Depending on the Rule-Meta data - it might launch up to a dozen queries and perform logic steps for each record. This is not the ordinary SQL Query.

Imagine if one record (for 1 field) takes 0.1 second to run. By referencing the function in 4 columns, this same function is re-run 4 times (0.4 Seconds) Against 50,000 records - this duplication of re-running the function for each column can really add up.

Possible Solutions: Researched Class Modules - There is a comment that the property Get, Let actually reduce performance. There are huge advantage of code documentation, documentation and centralization.It doesn't claim class modules reduce execution as each propery is returned. It also describes that Class Modules can't be called directly in a Query - unless each property is wrapped in a function.

Function Returns one String with delimiters: e.g 34;54;55;1 This single column goes into a Make Table (runs function one time per record) Then the D-Lookup is run against static local data. This pevented the function from being run over and over across the network linked data.

Final Solution: Eventually, the many hundred lines of VBA code for the Rules Engine will be converted into SQL Server T-SQL Functions on the server.For a Rule Engine development, Access has been great for a rapid protoype development and testing. The TSQL will be a final big step requiring re-coding. It is not currenty my option for the delivery time frame.

View 8 Replies View Related

Modules & VBA :: Subform Date Filter That Shows All Values If Left Blank

Mar 2, 2015

I am a bit of a novice when it comes to Access, but have managed to create a form with a subform embedded and various filters to show different data within the subform, including a date range filter. The code I have used for these filters is as follows:

Private Sub Command40_Click()
Dim strCriteria As String

strCriteria = createCriteria("[Introductions].Town", "List78")
strCriteria = strCriteria & " and " & createCriteria("[Introductions].Ownership", "List52")
strCriteria = strCriteria & " and " & createCriteria("[Introductions].Company", "List54")

[Code] ....

This all works fine, but I'm wondering what I need to add to this code to make it so that if the date boxes are left blank, records from all dates are displayed. At the moment I have to enter dates in order for it to work properly.

View 5 Replies View Related

Forms :: Closing Form With Subform

Jul 8, 2014

I have a form with a subform. There is a close button on the main form which I have applied a key command of Alt + c and when the focus is on the main form it closes the form as you expect. But if the focus is on the subform, using Alt+c does nothing. I am trying to give the user a way to avoid using the mouse (so they do not have to click on the button from the subform. Also it is confusing that the key command doesn't always work in the view of the user, and in reality he is on the subform.)

So my thinking is:

1. Can I create a close button on the subform (and give it the key command of Alt + c) and in the code close the form and subform, if so - how to I refer to the form/subform?

2. Is there some way to apply the key command of Alt + c on the subform to activate the close button on the main form?

View 4 Replies View Related

Modules & VBA :: DLookup With Multiple Values - Loop To Check Entire Table

Jul 14, 2015

I have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but how to write that as I have never used it before. Below is what I was using until this new request came up.

<code>
Private Sub txtloan1_AfterUpdate()
If IsNull(DLookup("[loan1]", _
"settlement", _
"[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then
Cancel = True
MsgBox "Test", vbOKOnly, "Warning"
End If
End Sub
</code>

This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.

View 7 Replies View Related

Forms :: Go To New Record On Subform After Closing Pop Up Form

Jul 9, 2013

I have a form (frmAuditEntry) that includes a subform (sfrmAuditDetail). The user will enter audit information on the subform. There is a check box on the subform to identify containers that had errors in selection. When the box is check by the user, a pop up form opens where the user can enter the details regarding the type of error. The audit number and the container ID are carried over from the main form to the pop up form to link the information.

This is the code I have in the AfterUpdate event of the checkbox:

Private Sub Errors_AfterUpdate()
Dim rs As Object
Dim strBookmark As String
strBookmark = Me.ContainerID
Me.Requery

[Code] ....

This works great and keeps the focus on the record the user checked having errors so the right information transfers tot he pop up form fields. When the pop up form is closed, the focus goes back to the subform but keeps the focus on the container ID control of the record that the user previously checked as having errors (hope that makes sense).

What I would like to happen is this: When the pop up form is closed, I want the focus to go back to the subform but to go to the last blank record on the subform (if the user moved off the record before checking the error checkbox) or go to a new record (if the user did not move to a new record already before checking the error checkbox).

View 1 Replies View Related

Forms :: Refresh Subform On One Form While Closing Another?

Apr 14, 2014

I have two ways to enter work placements for students, I need to refresh a subform on one form while closing another.

The issue here is the form with the subform may not be open as mentioned above.

So is it possible on closing one form to refresh a subform only if it's parent form is currently open?

View 1 Replies View Related







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