I have a form that is opened by the following code:
Private Sub Status_AfterUpdate()
If Status.Value = "WIP - Snagged" Or Status.Value = "WIP - Suspended" Then
DoCmd.OpenForm "NotesJobChanged", , , acFormAdd
Forms!NotesJobChanged!txtJobNo.Value = Me!txtJobNo.Value
Forms!NotesJobChanged!Date.SetFocus
End If
This 'NotesJobChanged' form has three text boxes on it - JobNo, Date & Note. The JobNo is autofilled from the code above. If nothing is entered into the other fields and the form is closed this record is entered into the database with only the JobNo filled in.
What I want is a way to force the user to enter data into these fields. I don't want to set the 'Required' property of the field to yes as it generates an awful system error message. If the user attempts to close the form without filling it out can they be reminded that they have to, but with an option then to close the form if they really don't want to enter any data? If they do chose to close without filling it out can it be prevented from being added to the database.
I have a tabular sub form, which is just used to show the user data. It is not editable, and the user can not do anything on the form other than choose a record from the PK, and thenview the data.
How can I prevent the a blank row from automatically being added to the bottom of the subform. I only want rows with data in them to be shown.
I have a couple different reference files that get updated each week. Sometimes there are missing data elements, so I'd like to structure a select query to show me those records that have blank elements but I'd like the similar records to be pulled in as well, so I can make a determination as to how to populate the blank records..
See attached example: I have a client ID reference table that gets populated with forecast owner names (individuals responsible for the customer) from a couple of different sources. Sometimes there are names attached and sometimes the field is blank.
How can I structure a query to show me just those Client ID's that have multiple entries with blank AND non-blank forecast owners? I'd also like to exclude single/multiple records where there are only blank records...
OK, I have been searching around on the fourm for over an hour now...I give up. If this is some where else, I am sorry.
I have a database of maintenance data. There are several columns that are usually filled in, some records have some columns blank. They are formated text because they hold letters and numbers (see pic).
I am using a form to query the table...no problem. The form has text boxes the user filter down the data
The problem comes are with the results of the query. Any record that has a blank column is not retuned. I am using "Like" so that the user can enter in partial codes. I know "like" won't return "null" records.... Help!
Things I have tried: 1)IIF(form field is blank, return table field, else use like command to filter) - returns nothing! 2)Like "*" & [Forms]![Fleetwide_data_Request]![MAL_CD] & "*" returns all records without blanks (i.e. missing data)
What is the best way to prevent widowed records? By widow record I mean records in the parent table that do not have a related record in the child table.
I have a tblOrder that is linked to a tblProductOrder by the OrderID field. I don't want the user to be able to create a record in tblOrder without adding some products to the order, which will be stored in tblProductOrder.
I have a database with several forms, tables, queries etc. and it works fine. One of the users of the database accidentally deleted a record which was displayed by a query. I have selected AllowDelections = No in all object properties. Despite this, the record got deleted. Is there a way to prevent such deletions? Kindly help.
I have a form, that once prompted, finds a certain record for the user. How do I prevent the user from being able to see any other records but that one, once the form is opened? The user needs to be able to edit the particular record they opened, without viewing or editing any other records in the database. Is there a way to hide the bar at the bottom of the form that allows users to manipulate through the different records?
I am using a data entry form to enter new data into one of the tables.How can we prevent entry of duplicate records? Duplicacy shall be checked on the first two fields only. Please help. regards bijon
Hello, I HAVE searched the forums for many days but I could not find a solution. Maybe I'm not sure what to look for...
The MAINFORM_1(single form view) is based on a Query and has a cbo named 'cbo_client'. This cbo is for a field named [client_id]. The AfterUpdate [event] of 'cbo_client' does a requery on a field in the the following SubForm:
In the SUBFORM_1(datasheet) (based on another Query) is another cbo named 'cbo_subclient', with a select statement of 'Select (all the needed fields) FROM tbl_subclient WHERE ((([tbl_subclient].[subclient_id])=[Forms]![mainform_1]![cbo_client])).
Everything (incl. the REQUERY) works fine, and all the tables gets updated with the records the way it should. No problems.
But this is where i get stuck...: Once the user has entered data in the SUBFORM_1 datasheet, and then >changes the 'cbo_client'< on the MAINFORM_1, it requery the subform like it should, BUT the previously entered data in the subform is already written to the table!
So, I need a way to prevent the data in the subform from beiing stored if the user changes the cbo on the mainform (for a second time). OR prevent the cbo on the mainform from beiing changed after the subform is completed.
I hope you understand what I'm trying to do...
Please, just a point in the right direction and i'll battle it out from there.
the proper code to prevent duplicate records in my form?
I'm using:
Private Sub Ctl_Lname_BeforeUpdate(Cancel As Integer) Dim dupCount As Long dupCount = DCount("*", "Clients", "[LastName]= '" & Me.[LastName] & "'" & " And " & "[PreferredName] = '" & Me.[PreferredName] & "'") If dupCount <> 0 Then Beep MsgBox
[code]....
"This name already exists in the database. Please check that you are not entering a duplicate person before continuing.", vbOKOnly,
I'm learning how to do Many to Many relationships and I think I've got the basic idea down, I am just not sure how to prevent the same 2 records from being associated twice.
For example:
My practice database deals with Authors and Books.
I was able to set it up so that Mike Gunderloy and Susan Harkins are both Authors of the book: "Upgrader's Guide to Microsoft Office System 2003".
I was also able to set it up so that Mike Gunderloy and Susan Harkins are both Authors of the book: "Automating Microsoft Access 2003 with VBA".
The thing I want to prevent is something like this:
Book: - Automating Microsoft Access 2003 with VBA
Authors: - Mike Gunderloy - Susan Harkins - Susan Harkins (Duplicate)
Good morning! I'm building a database for use by four technicians in a call-center type operation. The call records are purely transactional in nature -- once the call has been logged, the record is closed. If the customer calls again, a new record is opened.
The supervisor doesn't want the techs to be able to go back and edit a record once it's entered -- the records are for quality-control purposes and he doesn't want them gaming the system by going back and changing things after the initial call is logged (let's not discuss the issue of employee psychology, motivation, Office Space, etc.). So I'm trying to figure out how to do that; they all have wheel mice, and I can currently see all the records as I scroll back and forward with my mouse wheel, so I'm assuming they'll be able to, as well.
Im trying to prevent duplicate records based on a PO # (RA_PO_Nbr), entered from a form. I'm trying to throw a 3022 error when the user attempts to enter a record that already exists, but I dont necessarily have to do it this way.
Code: Private Sub cmdSubmit_Click() On Error GoTo cmdSubmit_Click_Error Dim db As Database Dim rst As DAO.Recordset Dim strSQL As String strSQL = "CompanyPOTable"
We are a travel company and I am just setting up a new database with two tables - [Client_Table] & [Enquiry_Table].
Most exisiting clients call in when they want to make a new enquiry so the 'user' can go it to a form which creates a 'new enquiry' for that client.
We also download 'new enquiries' from our website. This data includes info that goes in to the [Client_Table] and [Enquiry_Table]. They download in to a XL spreadsheet. Currently I copy and paste the data (not sure if there's a better way) from the XL speadsheet in to a query. This query creates a new client record & a new enquiry record which are both linked by a primary key [Client_ID].
This all works however the problem I have is that sometimes existing clients enquire through the website. They do fill in a field to say they're an existing client but if I paste them in to the query as explained above it creates a duplicate client record.
The only way around this I can think of is it to take out any exisitng clients from the XL spreadsheet first, search for their Client_ID and then paste these enquiries seperately with their Client_Id's in to different query which only adds a new record to the [Enquiry_Table] and links them to their exisitng record in the [Client_Table].
I have a table for storing vehicles. The table contains the following fields:
VehicleID Make Model Trim Build Date Begin Build Date End
Vehicle Name - a concatenation of 'Make' 'Model' 'Trim' 'Build Date Begin' and 'Build Date End'
The 'Vehicle ID' is the primary key, the rest of the fields can't be indexed with no duplicates. Is there a way of preventing one from saving a duplicate VehicleName on the table since it is a calculated field.
I have an Access front end to a table residing on an SQL server. I am accessing the SQL server through a VPN over a DSL line.
Now, I have some data I need to insert in to that table. It sits in a text file. All in all, there are around 9000 records in the text file.
I had excel reading the text file, then cleaning up the data, then I would link the excel table via ODBC, and link to the SQL Server table, then do an INSERT INTO ServerTable Select * from ExcelTable; That would take about 22 minutes to finish.
I cleaned things up a bit, using VB to directly read the text file, and I can insert in to the ServerTable directly via an ADO recordset in about 18 minutes.
I want to get the time down further.
Now, if I read that text file and use ADO, I can insert all the records in an Access table on network share across that same VPN in about 4 minutes, so I figure there is about 14 minutes of overhead in dealing with the SQL server.
So is there anything I can do to decrease the amount of time it takes to insert the 9000 records in to the ServerTable?
Thanks, David
PS. Oddly, I tried to limit the number of fields I data I upload, and found it actually took MORE time to send one field than it did to send all 30 some fields in the full dataset. I really can't figure that out.
Hi! I'm stuck and need some help. I have a list of about 1600 entries, all merchant names and i need to place an * at the beginning and end of each entry, example *Arizona Central*. Each entry has a different amount of characters...would any one know if there is a way of how I can do this easily in Access or Excel?
Hi everyone, Im new here, and wanted to get some help on understanding insertion anamolies, because the logic behind it I can't grasp, because I tend to think of work arounds to solve the issue, so I am wondering whether I could get some help on the matter.
how or what function (DLookup) should I use to prevent duplicate records based on multiple fields? I want to look at data in three fields that can't match existing data in those three fields. It's ok if one or two of the fields match but not all three.
I want to lock archive records so that they can't be edited, however I want them available to users for viewing. So for example if the file is "closed" the record can not be changed.
Been experimenting on some code to prevent deletion or changes to records that meet certain conditions.
The problem I am having is the code is contradicting each other preventing some of the records to be locked down.
Code: Private Sub Form_Current() 'Code 1 - Prevent edits if 21 day period has lapsed If TestDate2(Me.txtStartedHidden) > 0 Then MsgBox "Editing NOT allowed - 21 day time period has been reached" Me.txtBasic.Locked = True
[Code] ....
Rules are:
Edits allowed if invoice date not reached Edits allowed if within 21 day but NOT if invoice date is reached
Edits prevented if invoiced date reached and if records still within 21 days Edits prevented if invoiced date reached & records past 21 days
Table in my access 2010 is configured to have a unique records (no duplicates)which has now records more than 2000 so i copied the table and pasted Structure only. what i would like to have is that new table which presently is empty should not add any record which is already available in old table. While entering data in new table i would like users to see the error if they try to enter the record which was previously entered in old table.
