I have a code which cut the data from tables and insert to others. The problem is I don't know when I can do this process (when I will cut data and the same time some users lock editing records then I will get an an error). So my question is, there is some procedure which can check that some records in table are locked or not?
View RepliesWhat would be the Access 2007 VBA code for checking for duplicate records?
View 14 Replies View RelatedHi there,
I've created a database of our customers and the plan is to have a few of our members of staff working through it doing support calls - there's very little information to get written to the database - just a flag to show whether the customer has already been contacted or not.
The problem I'm having is that as there's a set call order, and as we're going to have multiple users working through it, there's nothing there to stop 2 or more users having the same record open at the same time and customer getting called more than once. I've looked into the built in access record locking, but that is not going to help much as it doesnt seem to prevent the same record being opened more than once - it just prevents them being updated.
Ideally I want the database to set a flag when a record is opened and unset it when the user moves to next record, and have the form I'm using to browse the records just skip flagged ones. However I have no idea how I would go about doing this as I've pretty much just started writing access.
Anyone able to help me here please?
I have a user that is unable to edit records, He can click in the fields but he could not delete or enter data in the fields. He has the ability to edit records but its fields are locked. I created a split database and created a secure front which I distributed it to multiple uses. The other users front end is working fine, expect for the one.
View 2 Replies View RelatedI'm trying to write some simple code to see whether two check boxes (named cbM001 and cbM011) have been checked and if so, then send out an error message. So far I have tried:
If cbM001 Is False And cbM011 Is True Then
MsgBox "M011 cannot be selected unless M001has also been chosen."
Exit Sub
[Code].....
With this I get "Run-time error 424: object require"
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'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.
I am trying to check if a record exists, but I keep getting a error!!!
Private Sub Command10_Click()
If DCount("*", "tbl", "[ID] = "") <> 0 Then
MsgBox "This record already exists.
Else
stDocName1 = "McrAddNewRecord"
DoCmd.RunMacro stDocName1
End If
End Sub
I want to check to see if ALL textboxes are blank, then issue a message. My "Select Case" statement and "If" statement does not work. How can I accomplish this ? Below is what I have.
Blank = Null
Select Case Blank
Case strChartOfAccts1,
strChartOfAccts2, strChartOfAccts3, strChartOfAccts4, strChartOfAccts5 & _
[Code] .....
I have a button on a form and I want the On Click event behind it to check whether Shift is also being pressed when the user clicks the button (or Ctrl, or Alt, in fact any damn key will do).
I can't believe that there isn't a function in VBA like the old 1980s BASIC 'inkey' function (etc) that just simply returns the code of any key being held down at the time of the scan. I shouldn't have to mess around programming other events like KeyDown and KeyUp when I don't particularly need the key-press to trigger an event per se.
I'm trying to get a spell check to work on a text box on a form. i've got it popping the spell check window after the box has been updated but i'd prefer it to just highlight the errors in red, rather than pop the spell check box. another problem i'm having is when it's finished spell checking the box it moves the form back to the first record. this is the code i'm using.
Code:
Private Sub txt_notes_AfterUpdate()
If Len(Me!txt_notes & "") > 0 Then
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSpelling
DoCmd.SetWarnings True
Else
Exit Sub
End If
End Sub
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
Me.Text11 = Nz(DAvg("[final whse-in diff]", "dbo_inventory", "[CAFETYPE]=" & Me.Text7 And "isnull(me.[DATE FIXED])=" & True And "isnull(me.DATE_IN)=" & True), 0)
I am getting a type mismatch error with this.
My question is:
1. is the syntax correct....
2. Is my way of checking for a value to be null correct....
Okay I have a Private Sub on form PlotF:
Code:
Private Sub SetCheck212()
If Me.[Check161] And Me.[Check169] And _
Me.[Check167] And Me.[Check181] And _
Me.[Check261] And Me.[Check189] And _
Me.[Check187] And Me.[Check195] And _
Me.[Check203] And Me.[Check201] Then
Me.Check212 = True
[Code] .....
Some of the checkboxes are locked as I want to force the user to check them on another form (InvoicedF) but I want them to be displayed PlotF as well.
So I want to call the Private Sub from another form. So that the Check212 still automatically checks to true with out PlotF open on the screen.
Is this possible, Do I have to make this private sub a public sub? If so how do I do this?
I am having trouble checking if the last array value is equal to 'D'.
This is what i have so far but it keeps saying that 'subscript is out of range'... 'g' by the way is equal to 1. It just really means that there's only one value in ArrAC.
Code : If Not ArrAC(g) = "D" Then
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 form that I allow my users to view but not to change any of the records.
As the number of records has grown it's become necessary to add a search function.
My problem is that because the form is locked it cannot be searched.
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 want to check if data entered in a form field is existed
The form is bounded to a table
I used this code
If DLookup("Telegram_Number", "tbl_Violation_Of_Building", "Telegram_Number Like " & Forms!frm_Add_Violation_Building!Telegram_Number) Then
MsgBox ("number existed")
Me.Telegram_Number = ""
Else
End If
everything is ok but if the data is existed the database show the message and clear the field but i'm getting a Run-time error
'-2147352567(80020009)': the macro or function set to the beforeupdate or validationRule property for this field is preventing [ISF] from saving the data in the field
I guess the problem because the form is bounded to a table so he will save automatically
My solution is to unbound the fields and save the data via vba but is there any solution with a bounded form???
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 want VBA code that will perform the following steps. The Table1 has following fields:
ScanDate,NewBatchNo,BatchNo,PolicyNo
1. First of all look into Table1 the scandate<=29082014 and then check if there is No NewBatchNo of the corresponding record then capture its corresponding BatchNo of each record whose scandate<=29082014
2.Then check for the batchno that we have captured in another table Table2 and if its present then return corresponding PolicyNo. Now keep on searching that policy no in Table2 as it could be present 50 times in the table and then return corresponding batch numbers in excel sheet of that Policy No as below:
BatchNo - Policy No - Batch No1 - Batch No2 - Batch No3 ---------------
I am looking to lock my database until the user puts in the correct credentials.
I have already put the form on Pop Up and Modal. They can still select the "X" in the top right of the window.
I have the following code which errors on line
Code : .OpenDataSource Name:=CurrentProject.FullName, SQLStatement:="SELECT * FROM [qryMailMerge]"
The error (Error has occurred: The database has been placed in a state by user 'Admin' on machine 'W74XXXXXX' that prevents it from being opened or locked) appears in the Word document.
Code:
Public Sub CreateWordToPDF(strWordFile As String, strPDFFile As String, strSQL As String)
'Call CreateWordToPDF("C:Doc1.doc", "C:Doc1.pdf", "SELECT * FROM [qryMailMerge]")
Dim objWord As Word.Application
Dim docWord As Word.Document
'Open MS Word using early binding.
Set objWord = New Word.Application
Set docWord = objWord.Documents.Open(strWordFile)
[Code]......
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
In an Access 2010 form is it possible to export select records and fields in those records to a specific location?
Code:
Set objDialog = Application.FileDialog(4)
With objDialog
.AllowMultiSelect = False
.Title = "Please select a File"
.InitialFilename = "C:"
.Show
If .SelectedItems.Count = 0 Then
MsgBox ("Action Cancelled")
Else
[code]....
The user can select the directory using the code above, but can specific fields in records be exported to a excel workbook in that selected directory?For example, if the are 5 records in the database can the fields LastName,FirstName,BirthDate in records 1,2,3 be exported to Setup.xlsx in that selected directory?
All seemed to be working well, however, I noticed that all my subtable records in the database are exporting with each Primary table record. In my output, I'm looking to see each primary table record followed by one or more subtable records from a one to many relationship.
(Office 2010) Access/Word
Private Sub cmdPrint1_Click()
Dim objWord As Word.Application
Dim docm As Word.Document
Dim db As DAO.Database
Dim rstLandSales As DAO.Recordset
[Code] ......