I have a Yes/No field in a table with all values unchecked by default. I ran a query to meet a specific criteria and now I want to check all Yes/No records of the result so they become Yes. Is there an easy way to do it? Because when you select the column there is no option to "mark all as checked".
View RepliesI am trying to write a query that will check all records in a table but exclude the 10 newest records in the table.
The table is from a stock program i have wrote for the company i work for (i am a novice access user). what ive been asked to do is write a duplicate order system that will flag up if the order has already been packed.
the table logs the [OrderID] with each item [barcode] scanned out with a barcode scanner what i want is a query that checks the OrderID for a duplicate entry in the entire table but because the OrderID is entered with every item scanned i want to ommit the last 10 records as prety much no order has more than 10 items i understand this may not be 100% fool proof but it is close enough.
The other option is to have it ommit all records within the last 15 minutes there is also a [Time] and [PackDate] Field which im guessing could be used for this the time field records Now() and the PackDate records Date(). After searching the web i cant seem to find anyway to ommit the last 'n' records and the few things i have found with the Date("m",-15,Date()) doesnt seem to work
how can i make a image appear in my form when there is a check in the check box from the table?
View 14 Replies View RelatedI have button on my form that uses a selected record in a listbox (non multi-select) to run code that updates a table. The code itself works great so long as there is a record selected in the listbox. If no record is selected an error is thrown. I'd prefer the end user not to ever see that error. If the end user clicks the button and no records are selected, I'd like for nothing to happen at all. I've tried several variations of checking the listbox with an if / then statement to see if records are selected and if so to exit sub, but they all throw errors when no record is selected in the listbox.
Code:
If IsNull(Me!lstHeatTreatments) Then
Exit Sub
End If
[Code].....
I have an update query that adds a date to a field which works perfectly but I am wondering if I can get it to check if there is a date there already, if so add another field and put the date in that? Here is my code currently:
Private Sub Command33_Click()
Dim t1 As Date
t1 = Date
CurrentDb.Execute "update tblTest set tblTest.Date2 = #" & t1 & "# WHERE ID = " & Forms!frmSwitchBoard.txtID
End Sub
I have a form open, but want to print only specific fields in each record. Been trying lots of filtering methods to no avail as I am a novice at filtering.
I can filter records, but cant figure out how to filter specific fields from those records.
My project goes something like this:
Each record in my form consists of listed items like on an invoice. However, not all the items on any given invoice will be printed. Therefore, I set up checkboxes beside each line, so for each invoice, I only tick the items I want to be printed from that invoice. However, I just cannot yet find a way to print the selected items.
I need to have a query-based form open and allow users to check off (or otherwise indicate) which records they want to update.
I tried having a check box on a continuous form, but when you check one, you check them all. I did a search on this, and saw that if you make the check-box bound, then that will work. But I don't know what to bind it to. I don't want to add another field to an already large and complex table, and I don't know where to put it if not in main table.
I'm an Access newbie, self-taught. I can make a basic form to input data.
What I'm trying to create is a tool for assessing aquisition of student skills. I want to be able to check that a student knows the name of a letter or the sound of a letter - but have the check recorded as a date in the datasheet.
Is this possible? And, can you explain to me how to do this in language I can understand? I'm not a programmer you see
Thank you,
Amie
I have a form to enter archaeological features in a feature table. Each feature is present in one or more archaeological strata, for which I have a stratum table. There are 41 strata in total named 1A, 1B, 2A etc. On the form I want a checkbox for each of these and then to make it add a record to the stratum table for each of the selected strata. The stratum table and feature table are connected by the feature_primary_ID (key in feature table), so when making a new record I need this primary ID and the respective strata to be entered in the fields.
At the moment I've got something like this:
Code:
Private Sub Check1_Click()
If Check1.Value = True Then
DoCmd.RunSQL "INSERT INTO tbl_FEAT_STRAT (feature_primary_ID, stratum_ID) VALUES ((feature_primary_ID of present record),Label2.Caption)"
Else
DoCmd.RunSQL "DELETE FROM tbl_FEAT_STRAT WHERE feature_primary_ID = (feature_primary_ID of present record)"
End If
End Sub
For starters, I dont know how to get the feature_primary_ID from the features table (also represented in the form as a field) and use it to make a new record in the strata table.
Second, when using this code I need a copy for each of the 41 strata checkboxes. Creating lots of redundant code. Easier would be a generic code that loops through all the checkboxes after they've been checked and then add new records for each of the checked boxes. Tried something with a command button, but couldn't get it to work.
I need to create a record of Users. I created a form with fields containing Username,FirstName,LastName & Email Address wherein the UserName is equals the value of the fields FirstName & LastName. I have a save command button and when I click it, I want it to be able to check if the new entry has a similar UserName that exists in the database, if it exists then it will allow me to edit the UserName and then save the data otherwise will allow me to save it.
View 3 Replies View RelatedI have a table of data regarding companies (contact info, etc). The company I work for provides these companies with up to 10 different products. On my input form, I have created 10 check boxes (and thus, 10 columns of Yes/No data in the corresponding table).
Each company has at least 1 product checked off, and up to all 10.
I would like to create a combo box that lists all 10 products, and upon selecting one, a list box then populates with the names of the companies (primary key) that use that specific product.
So, for example,
Company A buys CATS and DOGS from us
Company B buys DOGS
Company C buys CATS and ELEPHANTS
Combo box options: CATS, DOGS, ELEPHANTS
If I choose DOGS, then a list box gets populated with:
Company A
Company B
and when I click either of these, the record should be brought up for the respective company.
All of this should take place in the header of the form, while the form itself can be updated based on search selections.
I have a form with 22 subforms (in about 11 tabs). Each one of these subforms has a controlling field in the main form (table) that dictates if the associated subform needs to comply with data entry rules or not.
For example:
Field in main form cboMed (Yes; No)
Subform: frmSubMeds has a list of medications
I want to be able to loop through all the records of the subform and determine if they are complete.
This check is run from a command button; that should also consider the value in the combo box. i.e. Check only if the combo has a value of "Yes"
Since i have 22 subforms and have to run the check on everyone of them, i have used the tag property to check if the record is blank or not. This is what i have so far
Code:
For Each ctl In Me.Controls
Select Case ctl.Properties("ControlType")
Case acSubform ' only look for subforms
If ctl.Enabled Then
If Not (IsNull(ctl.Properties("SourceObject"))) Then 'if it is a subform type
[Code] ....
The problem with this is that it only looks for the selected record in the subform and doesnt look for ALL the records.
I have a parent form and connected to it is a subform. On the parent form I have a checkbox which enables and disables fields on the parent form and also hides the subform.
What I want to do is when the user unchecks the checkbox, this action also deletes the associated subform records, if there are any.I'm sure that this can be done with an SQL Delete query in VBA.
I am using the following code to check for duplicate tickets when importing multiple records into a datasheet view form by using the paste append function.
Code:
Private Sub Ticket_Number_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
If DLookup("Ticket_Number", "Record_Store", "Ticket_Number= '" & Me.Ticket_Number.Value & "'") > 0 Then
Cancel = True
MsgBox "There were import errors, please open View Import Errors above."
End If
End Sub
The form is used to insert multiple records into the database at a single time.
That codes works to check for duplicates. And if there are none there are no popup messages.
If there are duplicates though it gives a popup for every single Ticket_Number that is a duplicate.
I am wondering if there is a way for it to give only a single popup once it completes checking all the records to be imported for duplicates.
I have a parent form which has a yes/no checkbox in the form's record source. Then in that parent form I have a sub-form. If NO records exist in the sub-form I want the checkbox to be UN checked. If records DO exist in the sub-form, I want the checkbox to be CHECKED.
But I want this to happen as records are added or deleted from the sub-form. In other words, if the parent form is opened and no records exist in the subform then the checkbox should be unchecked. But as soon as the first record has been entered in the subform, the checkbox on the parent form should be checked. Likewise, as soon as the last record has been revoved from the sub-form, the checkbox on the parent form should be unchecked.
What code do I need to accomplish all of this?
I am looking for a query that will return records from a table that have related records in another table. Opposite to the Unmatched Query Wizard.I have two tables: tblSupplier and tblSupplierProducts.The two tables are related by the field "SupplierId".I need the query to only return Suppliers that have Products.
View 3 Replies View RelatedI have two tables:
one with all the policy numbers, and another with a few of the policy numbers. How would i make a query, that each policy number is in the other table, then it returns "Yes", otherwise it returns "no"?
Hi all
I'm looking for some code that will do the following
If a table exists called "data" then delete it
Any ideas??
I have a survey database that I've been using for the last year for monthly auditing of employees files. I need to be able to get monthly audit scores for each employee but grouped by their manager. The problem I'm having is employees have moved between managers throughout the year, so employees that are listed under Manager 2 now were actually working for Manager 1 when the audits occurred.
ie. Audits occurred Jan - April for Employee 1 while they were assigned to Manager 1. Employee 1 moved to Manager 2s team in May. So when running monthly reports for the year Employee 1 audits should fall under Manager 1 for Jan-April and Manager 2 for May-Dec.
I do have a history table set up like:
tblEmployeeHistory
ID (PK)
EmployeeID (FK to Employee table)
ManagerID (FK to Manager table)
MoveDate (date employee assigned to manager)
The Employee table is set up like:
Employee ID (PK)
EmployeeName
ManagerID (FK to Manager table)
The Manager table has the ManagerID and ManagerName.
That's the employee side of things; then I have the tables that store the audit results:
tblAudit
AuditID
FileNumber
AuditDate
EmployeeID (FK to employee table)
tblAuditResults
AuditID; QstnID (Composite PK, QstnID is FK to tblQuestions)
Answer
How can I use AuditDate and MoveDate to relate audits to the managers the employees were under when the audits occurred?
Hi,
I wonder if any of you could help me with what I assume to be a simple request, but one I've stuggled to find an answer to in the 'help' searches that I've done.
I'm working with an Access 2003 database that was originally designed to use a copy of a live table held elsewhere (this copy only used a subset of the fields from the main table). I now need to change the database so that it references the live table only, to ensure data integrity. To do this, I want to remove the 'copy table' and make all forms, queries, reports, macros etc. refer to the live table instead.
Is there a 'where used' procedure that I can run to find all the objects that use this table, rather than my having to go through every single one and check if this table is referenced (there are hundreds of such objects in this database)?
Thanks for your time,
Hi
I have a problem where a user would enter a project code eg 113-099. I put in a list box that filters all the project codes for instance if the user enters 1 it will show all codes starting with a 1 and every additional number they enter it filters the list box to show which codes already exists! I hope this makes sense.
The problem i have is i need to check when they enter the project code that it is a unique value. The Database developer we got in didnt index this field as no duplicates allowed and as result we have large amount of duplicate project codes.
I have started to experiment with Dlookup but with no great success.
If anyone can help it would be much appreciated as i dont have much experience with access.
The field name on the form is
[forms]![projects_frm].[project_job_number] and the table which stores this information is [projects]
Is there any way to check if a particular table already exist in MS Access Database using ACCESS VBA?
Actually I'm creating temp table on the file but I want to check if temp table already exits then need to delete before creating.
e.g. I can check if query defniniton already exists using below code:
Code:
If .DCount("[Name]", "MSysObjects", "Left([Name],1) <> '~' AND [Type] = 5 AND [Name] = '" & strTempQueryName & "' ") <> 0 Then
.DoCmd.DeleteObject acQuery, strTempQueryName
.CurrentDb.QueryDefs.Refresh
End If
hi,
i need a db to monitor attendance (I know there are examples that do this but I need to satisfy my own curiosity and haven't seen one exactly like i need). My plan is to set-up the tables as below. I now have one-to-many relationships. There are many students. each student will study many courses.
I would like a form that shows the student info and a subform that shows all the courses they are on and weeks 1 -10. i could then use the yes/no attribute to record an abscence. later i would then need a query to see which students had missed two weeks in a row or more than 3 in total.
I think i need a form bound to tblStudents with a subform control that is displaying a form that is bound to tblStudentCourse. i am then a bit stuck as i need another form that will display the course records for each student. am i off track??? any ideas would be great. thanks.
tblStudent
StudentID (PK, number)
FirstName
LastName
tblCourseID (PK, number)
Week1 (yes/no)
Week2 (yes/no)
etc.
tblStudentCourse
StudentCourseID (PK, autonumber)
StudentID
CourseID
I need to add a Yes/No field to a table. I know I can do this in design view. Next I want to loop through the table and set it to Yes if certain fields are null. What I need help with is the VBA looping part and setting the boolean field to Yes if the fields being checked are null.
View 3 Replies View RelatedHi,I've searched forums for something similiar, but didn't find anything (or maybe missed, my english is bad), so sorry if this was asked before.I've just started to use MS Access and i need something i can't makeThe thing is that my table has a column with data that has to be like this:same_text_always={{{{[any_text];any_number(nr_next);any_nr;any_nr}};any_nr};{{{[any_text];any_nr;any_nr;any_nr};{[any_text];any_nr;any_nr;any_nr}};any_nr};{{{[any_text];any_nr;any_nr;any_nr};{[any_text];any_nr;any_nr;any_nr};{[any_text];any_nr;any_nr;any_nr};{[any_text];any_nr;any_nr;any_nr}};any_nr};{{{[any_text];any_nr;any_nr;any_nr};{[any_text];any_nr;any_nr;any_nr}};any_nr}}Is it possible for access to check all column data to be like this and display all mismached fields in a new datasheet?any help, hint, tip would be very helpfull, thanks.I'm using MS Access 2002
View 2 Replies View RelatedI have 2 tabels, Docs and People
Each table has about 15 check boxes to further specify the doc/user.
-in tblDocs which groups of users this document is intended for and
-in tblPeople which document the user should be trained in and a tickbox if training has been completed.
e.g. if tblDocs chkA is ticked then all people that have there chkA ticked should be trained in that procedure, if tblDocs chkS and chkE are ticked, all people that have either chkA or chkB in tblPersons ticked must be trained.
What I'm looking for is a query that can can check this on a form.
I know how to set it up so that all values from a form with all details of a person are transferred into a query, but I just cannot work out how I then check the tblDocs for documents that do NOT have the corresponding boxes ticked. In other words, I'd like to see if people have been trained in all documents they should have been trained in.
I have no idea how to handle that.