I have a form that I want to filter out certain records based on if a field has data in it or not. I tried using a macro and putting the field equals "IsNotNull", but that didn't work. I just asked me what "IsNotNull" is suppose to be. correct way to do this via macro?
I tried searching but was finding VBA with other filters being applied (which is not the case).
I am fairly new to Access and trying to populate a text box based on whether other textboxes throughout the form contain a date or are null, so as I update the progress of work the textbox that I have located in the header will indicate the status of work flow, this is what I thought might work but I seem to be doing something wrong.
Code:
Private Sub Form_AfterUpdate()
Status = IIf([Date_Raised] Is Null, "New", "")
Status = IIf([Date_Raised] Is Not Null, "Open", "")
Status = IIf([Approved_Date] Is Not Null, "Approved", "")
Status = IIf([Actioned_Date] Is Not Null, "Actioned", "")
Status = IIf([Tested_Date] Is Not Null, "Tested", "")
Status = IIf([Closed_Date] Is Not Null, "Closed", "")
End Sub
I have a ticket database where tickets are added to access with no priority scores on them. Over time, meetings and discussions, those tickets are given scores through a form on my database. What I would like to do is somehow add a timestamp that shows exactly when a ticket went from being unscored, to having a priority score assigned to it. Note, before the ticket is scored; the Priority Score field is still blank. Null?
An idea I had was to somehow put a timestamp on the "Priority Score" field in my form that will only timestamp when that field goes from being blank to when it gets a number for the first time. It will not change the timestamp when that number is changed down the road. (We edit the priority scores, but I only want to know when the ticket was actually scored for the first time. This is for reporting purposes, how many new tickets we score each month, etc)
Could I do something along the lines of assigning code to the BeforeUpdate value of my Priority field on the form? Although I'm assuming this would put up a timestamp any time the field is altered afterwords also.
I haven't programmed using Access in about 10 years and seem to have lost all knowledge of it.I'm struggling to make a really simple application. I need to keep track of which serial number is attached to which order.All I want is for me to be able to scan a barcode (or manually type the numbers) into a 'packschein' (packing list) and then to scan all the barcodes of the products' serial numbers relating to this packing list. Then preferably simply press the enter button or even better scan a barcode which launches the code to save the new entry.
So a packing list can have 1 or more serial numbers.However, the way I've set it up, for some reason it requires a packschein number (good), but then does not require a serial number. I have this feeling I messed up with the way the tables are meant to be linking to one another.
I have 2 fields in my form.. on if for date (date picker)..
The other one is for day..
I am using this atm:
Code:
Private Sub okt_courtdate_AfterUpdate()
okt_courtday = Format(okt_courdate, "dddd")
End Sub
So.. after i pick my date.. when i click tab.. the day field in form will be filled with correct day of that date.. The problem is..
I want to change days to my countries language.. example if it's monday, i want it to be "isnin".. I need to do this for all 7 days of the week.
I'm using Access 2007. I have a few problems:
1. I have a switchboard. I want to click a button, that opens a form with a dropdown list, when I make my selection, it opens a subform in add mode, but the linked field in the subform isn't empty, but filled with the mainform's field value that I selected?
OR
2. Is there a way for me to open a form in add mode, add data to it, click the add button (I will add an add button) that allows me to add again, but this time a particular field is not empty, but filled with selected info from previous selection?
Say for instance, I have 2 fields (both combo box fields), I click add, made selections for both fields, I click add again, but this time one of the fields stays constant like it's already been selected. It's filled with what was selected from before.
Either of those 2 - which ever is simpler.
I have a form that shows records from ADODB recordset.When I try to apply filter to the underlying recordset it works all right but the form doesn't reflect the changes. It shows same rows as before filtering. In debug I can see that the recordset contains only filtered records. Me.Refresh (Recalc, Requery) doesn't work.
Code is as follows:
Dim rs As New ADODB.Recordset
rs.Open sql, conn, adOpenStatic, adLockOptimistic
Set Me.Recordset = rs
Sub combo_AfterUpdate()
Me.Recordset.Filter = "CompanyNo = 123"
End Sub
The form is in Continuous forms mode. I cant use DAO because the data comes from SQL server user-defined function.
I currently have a form that only shows data based on a specific record id. I have placed a list into that form though that has multiple record ID's listed ( It's a log). How do I get a List to Filter to just the Record ID that corresponds to the current form Record.
What I have are as follows:
A form that has client information : Address, phone numbers, Etc.
On that form I have Pages. one of the pages contains a List.
That List however contains information from multiple clients based on Point of Contact ( Call Log)
What I want is a way to narrow the List to only show those that corresponds to the Current Client Record.
Is this possible? If my approach is wrong, which is a better way?
Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:
Categories Table:
CategoryID (PK)
Description
Users Table:
UserID (PK)
Username
CategoryID (FK on Categories.CategoryID)
Jobs Table:
JobID (PK)
CategoryID (FK on Categories.CategoryID)
JobDetails Table:
JobID (FK on Jobs.JobID)
UserID (FK on Users.UserID)
Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:
SELECT Users.ID, Users.Username, Users.CategoryID
FROM Users, Jobs
WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));
All the tables are linked with relationships but my SQL isnt so hot!
Any ideas as to how i would do this and get it working?
Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).
Thanks in advance,
Chris
Hi
I have been searching this forum for 3 hours for a solution, some come close others are pure gobble de gook to me.
I have on a form 1 combobox where a name is chosen, from that choice I would like the address, suburb, state etc automatically placed intheir relevant fields.
I have tried =DLookUp("[PropertyAddress]","tblProperty","[Property]") in the address textbox which will only bring up the first recod's address from the table, if I change the name in the combobox the address doesn't change.
What am I doing wrong, I have designing databases in Access for a total of 7 days now and have been going fairly well with some tips and code snippets from this forum but this has got me stumped.
Cheers
optidisk
I have a couple of fields in Access. To explain I will show here the fields I have and their datatype
ID: Autonummer
Amendment: Text
Basis info: Yes/No
Sex: Yes/No
Period: Yes/No
Salary:Yes/No
Shift:Yes/No
Now, in the amendment field, I've inserted a combo box, with 3 options in it. Let's say option A,B and C...The thing I want access to do, is, when option C is selected, I want fields Period, Salary and Shift to be automatically "Yes".But if option A or B is selected, the the user must choose what the other fields are going to be either Yes or No
I almost have this form done.
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
:confused:
I have a form that is almost complete
I have a form with a combo box, and 2 subforms from it. When you select in the combo box (Group), it brings up the choices (Sections) for that group in a subform. Then there is another subform, that is supposed to bring up a series of questions that are related to the section that has been selected. The question field has a drop down to a list table, that has all the questions. Once the question has been selected, it stores in a Master table. The relationship is there. If you try to select any questions that do not pertain to the section, it gives you an error message.
What I need it to do, is when the section is selected, filter out the questions that pertain to that section, and have those questions be the only ones available in the drop-down list to choose.
I know that I need to have an After Update code once the section has been selected, but not sure how the coding should be.
I hope that someone can help with this!
I am working on a scheduling form that uses 3 combo boxes to select the people being scheduled. The form is rowsourced to a table of training sessions with a field for each of three crew positions being trained. One combo box is used for each of those fields. The row source for each combo box is a query that returns a list of people due for training, not on vacation that date, correct crew position, etc.
The problem is this: I want to eliminate the people already sceduled in prior sessions from the list. I've tried to put a "not" criteria on the rowsource query but that did not work.
I've been away from Access for a few years but I vaugely remember problems of putting criteria on the field you are filling. Anyone have any ideas?
Is it possible to use Filter option in my form without using Query Wizard? - So that the user would be able to edit and change the other field on the same form
View 2 Replies View RelatedI try to create an error message if a user fills in a date field and leaves a combo box with wrong value.I have no clue how to use "Is Not" to check the combo box.The date field that will be filled in is called "Sent to Check" and the combo box is called "Status Case" and the value should be "Sent For Check" If the value in the combo box is different, then the back ground should change to red and get a message.This is the code I have so far that does not work:
Code:
Private Sub Test__date_started__AfterUpdate() 'XXXXXXXXXX working onXXXXXXXXXXXXXX
If Not IsNull(Me.[Sent_To_Check] And Me.[Status_Case] IsNot "Sent For Check" Then
MsgBox "Status Case be set to Sent For Check!", , "Incomplete Form!"
Me.[Sent_To_Check].BackColor = RGB(255, 0, 0)
Else
Me.[Sent_To_Check].BackColor = RGB(255, 255, 255)
End If
End Sub
I would like to define a field (mailingaddresscounty) as type lookup using a field (county) from another table (Zip). But instead of the user selecting from a long list, I would like the selection to be automatically made based on the value of another field (mailingaddresszipcode).The two tables are Organizations and Zips. The user enters the 9 digit zipcode in the organization table (mailingaddresszipcode). I would like the county field in the organization table (mailingaddresscounty) to draw from (link to?) the corresponding 5 digit zipcode in the Zip table (zip) and return the correct county for that zipcode.
View 1 Replies View RelatedI have a form with datasheet view and I need to make user that user fills in all fields before he moves on to the next row. how to catch the moment of moving to another row?
View 3 Replies View RelatedOne of the managers has requested to set up a form to enter the date at the top of the form and then enter in data for all the plants below for that day. I thought I could just use a form/subform combo with the date (and any other information repeated to all the plants), but the catch is, he wants a list of the plants to automatically populate when he enters a date.
The table would look something like this:
Form
Date
---------------------------------------
Subform (with all Plant names pre-filled for this date -- same list each time--and allowing him to fill in Sales and On Time Delivery).
Plant___________Sales_________On Time Delivery
Indianapolis
Detroit
Memphis
Tampa
Example with data
Form
4/20/2015
---------------------------------------
Subform
Plant___________Sales_________On Time Delivery
Indianapolis_____$100000 (manually entered)_______95% (manually entered)
Detroit_________$120000 (manually entered)_______98% (manually entered)
Memphis_______$130000 (manually entered)_______99% (manually entered)
Resulting in table records:
Date----------Plant---------Sales--------------On Time Delivery
4/20/2015____Indianapolis_____$100000_______95%
4/20/2015____ Detroit________$120000_______98%
4/20/2015____ Memphis_______$130000_______99%
I usually would just create a sub-form with drop-downs for the plant names, but I've never started with a pre-defined list of records.
I have a query, that I have a criteria to show appointments in the past (< Date()) but one result doesn't show up although the appointment end date is a past date, it only shows up when I fill in a field that is in another table that is joined and part of the query. But there's no criteria there for it to not be null.
View 3 Replies View RelatedI am trying to select a value from one combo box and on the basis of this selection the other combo box show only those values which have link to the value I have selected.
View 14 Replies View Related(MACROS ONLY)I am looking for a way to open a form in order to add a new record. The idea is that I open up the form with a MemberID and possibly the name already filled in on the relevant form. It is merely for ease of use regarding the user.
I have got as far as opening the the new entry form. I just need to pass the MemberID into the relevant field. If I use the wizard it is just finding a record of a pre-filled entry.
I tried to check if all required fields have been filled before saving the record using the following code.
Private Sub txtReportNo_BeforeUpdate(Cancel As Integer)
If IsNull(Me.cboMainCat) Then
Cancel = True
MsgBox ("Please enter Data for Main Cat")
Me.cboMainCat.SetFocus
End If
Repeat above code for all required controls here.
End Sub
It did not work with following error code.
Run-time error 2108
You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method.
The error message contradicts my original intention that prevents from saving without all required fields have been filled. Is there any way to get rid of this error message?
In addition, under the value of certain field, I need to check additional fields under that scenario. Is any additional code required to add in for that purpose?
I have two fields that are in short time format, and are saving the information to the table in short time format.
I then have a field that I have set to auto-fill under the chosen circumstances, with a text roughly as follows
Code:
Dim Story As String
Dim IDepTime
DimADepTime
IDepTime = [InstructedDeparture]
ADepTime = [ActualDeparture]
Story = "This person was instructed to leave at " & IDepTime & " but did not do so, instead departing at " & ADepTime & "which caused us a problem"
me.txtnarrative.value = Story
End Sub
This all works, however the times being displayed are in hh:mm:ss format, when I only want hh:mm format - how do I tell it the format I want?
I am trying to open a word document with corresponding data filled from the text field of a form. I managed to get the word document but I don't know how to give a variable in word document.
View 2 Replies View RelatedI have a database where multiple users log in and work in 1 of 2 different forms. The 1st form in for initial data entry where users enter data then senter there initials and save each record. the other form is for QC'ing data that has been entered and they will add additional data to the form, then enter their initials and save the record.
How can I make it so that I can have a user once they log in to the database, it will autofill their initials? I have used a dropdown box before but it is not useful for people who have the same first name initial or worst, the same first and second initial because they end up selecting the wrong initials.
I do not currently have it set up where I have a login screen, I just have only forms visible to users when they enter data. Any easiest way to go about making this happen (I am just trying to shorten the amount of data entry that gets done).