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.
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"
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.
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
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?
I need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.
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?
I have a form in which new equipment is added. I want to be able to check when the ID number is added (first field on the form) that there is not a duplicate ID number. If I wait till the end of the form and save, then Access tells me that there is a duplicate present but by then I have spent time putting in all the rest of the data.
If have added this code in the before update section for the relevent field, but it doesn't seem to work. Can someone please help clarify or suggest an easier way
I'm new to using VBA for MS Access. May I enlist your help, Please.
I'm working on a human resources database for a group of employees. I'm trying to automate their login process after they have opened the database so that each employee's privacy is respected. Using an initial form, employees are separated into "New User" or "Returning User". My problem concerns setting up the "New User" in the database.
The New User is directed to form "frm_NU". The form has several unbound fields which are later carried to the table "tbl_Employees". Two of the fields on the form are used to create the EmployeeID. Those are Initials [INI] and Phone Extension [EXT]. The EmployeeID will be used in a filter so that only that employee's information is made available to the user (simulated Privacy).
Because the employees are unfamiliar with MS Access and its cryptic error messages, I need to create a message box which will give a plain English warning when a user attempts to use an existing ID to create a new record. [EmployeeID] is a field in the table. it is set as a key and does not allow duplicates.
When a new user enters his/her initials and phone extension I want the new ID to be compared to the table and if found, a message should appear. This is a preemptive strike to prevent the user from getting to the more cryptic MS Access error message.
I've tried using Dlookup and Dcount to try to pass some meaningful data to the If statement variable VarX to trigger the message box; assuming that Varx should be something other than Null or Empty
If VarX <Is Not Empty> Then X = MsgBox("Another record already exists by that name", vbOKOnly) Else Y = MsgBox("Passed", vbOKOnly) End If
I keep getting an error at the DLookup formula or DCount formula saying: Run-time error; 2001 You canceled the previous operation. It also appears that the DLookup never looks up anything nor does DCount count anything even though I'm purposely entering duplicate information which exists in "qry_Employees". I was expecting VarX to be either the duplicate ID or a record number or record count or something.
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
I would like to run an append query but only after I have checked a table for existing data. I want to see if a ralicarID exists in a table and if it doesnt the query should run. if the railcarid does exist but also has a check in and check out date then the query can also run.
I was thinking something like this
Dim RailcarInDatabase as number ' run query here that checks for the railcar id and returns a value if railcarID in table then RailcarInDatabase =1 else RailcarInDatabase =0
And if If RailcarID in table and DateInShop is not null and DateOutShop is not null then RailcarInShop = 0
Something like that which will allow me to run the query only if the car is not in the database or it is currently not in the shop.
I'm trying to create a report that does the following:
If the term "Other" is selected in the Time1 field, then the Time1 field will not be visible, but the field Other1 field will be visible and if the term "Other" is not selected in Time1 field, then the Time1 field will be visible and the Other1 field will not be. This is what I have for VBA code, but it is not working.
If Not IsNull(Me.Time1) Then If Me.Time1 = "Other" Then Me.Time1.Visible = False Else Me.Time1.Visible = True
I made this code in excel, and I'd want to get it working in access.
Sub Satunnaisluvut() Dim OmaAlue As Range Dim Solu As Range Set OmaAlue = Range("A1:A5") For Each Solu In OmaAlue If Solu.Value = "aa" Then Solu.Value = "bb" End If Next Solu End Sub
How do i set the wanted field? And how about the loop?
I am creating a report that has a filter based on 3 separate listboxes. The user has the option of choosing one or more filter criteria from each listbox. The trouble I am having is if the user only chooses one filter, I need to adjust my filter string. This is the code that generates the filter:
I need to get this syntax right. I have something similar that worked before to open a report but now I am using the same code structure on opening a form and I can't get it.
[prikey] is an autonumber and that has given me trouble before with the syntax. [EstimateFlagCleared] and [WarrantyFlagCleared] are Yes/No fields.
Dim maxFlag As String Dim flagCriteriaWarranty As String Dim flagCriteriaEstimate As String
I have a form where a user enters data. One of the things the user enters is a "lot size". I need this lot size field to be checked against a query in the database to determine if the quantity is acceptable or not.
The order number in this query is in certain cases missing a leading zero, so I need to truncate this from the user entered form field.Finally I want the user to be notified if they are trying to exceed the lot size in the query.My code is as follows:
Code: Public Function RemoveFirstChar(RemFstChar As String) As String Dim TempString As String TempString = RemFstChar If Left(RemFstChar, 1) = "0" Then If Len(RemFstChar) > 1 Then TempString = Right(RemFstChar, Len(RemFstChar) - 1) End If End If
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 ??
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
The following code is giving me a "Run-Time error '13' Type mismatch. I have tried isolating both criteria and they seem to be fine but joined together with "AND" they error. Workdate is a Shortdate. Flightnumber and flightID are numbers. FlightID source is a cmb within my form.
Private Sub FlightID_BeforeUpdate(Cancel As Integer)
If DCount("[WorkDate]", "Main_tbl", "[WorkDate]= #" & Me.WorkDate & "#" And "[FlightNumber] =" & Me.FlightID.Column(0)) > 0 Then Do this.... End If
I have been trying to find a solution to why I can't get a Dlookup with multiple criteria to return the value I need.
Essentially I am trying to use an Order Number to find the item number which is contained within another table. However the order number has multiple lines (suffixes) which alter the item number. Therefore I am trying to have the item number be populated by the correct 'combination' of Order Number and line ("suffix").
I have managed to use the Dlookup in the after update of each box of the form separately and they retrieve values in the table correctly:
Afterupdate of main order number:
Code:
Private Sub OrderNumbertxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", "[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'") End Sub
Afterupdate of suffix:
Code:
Private Sub SuffixTxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", "[suffix] = Forms![**INPUT]![SuffixTxt]") End Sub
However when I combine them as follows in the afterupdate of the Suffix box I receive a "Run-time error '13': Type mismatch"
Code:
Private Sub SuffixTxt_AfterUpdate() ItemNumbertxt = DLookup("item", "dbo_job", ("[suffix] = Forms![**INPUT]![SuffixTxt]") And ("[job] = '" & Forms![**INPUT]![OrderNumbertxt] & "'")) End Sub
I think the reason is something to do with some being numbers and some being a combination of text and numbers (based on the replies of other topics), but have been trying to modify these slightly and can't get it to work still.
Also the Order Number is a combination of letters and numbers (normally in the form of AB12345678), the suffix is just a number between 0-9 and the Item number it finds is a combination of numbers and letters.
I have a form where varying layers of information can be entered. In some cases, a user may know all information or only part. If i tie the query to the form, each field has to be populated for it to work. how do i set the query up to effectively use the information available and not require all information?
I have tried setting VBA code as follows:
If Me.Combo1 = Null Then Me.Combo1 = "*"
However, when i do this, it updates the form field with an asterick/wild card but does not include in query. yet, when i update the form fields with data (not asterisk/wild card) the query runs.
