I need a query's field to change to what is selected in a combo box.
Example,
I select product 1 in the combo box on a continuous form, after the update there is a requery. The form now displays the data from product 1.
I have tried putting [forms]![form1]![combo1] into the field box of the query. But this only makes the form display "product 1" in every row, it does not take any data from the table.
I would like to change the background color of a field that is the result of a Unique Values query. I am trying to get a list of invoices where all the line items are approved. I can't seem to get it to work the way I want because if even one invoice line item is approved it will show up as approved.
Is there a way to change the background color of the invoice field to red if ANY of the Approved line items are = False
I have a column "CAT" each time that CAT switches between 0 and 1, I would like my query to auto-create a "grouping" and increment the group by 1. What I am looking for is the output as shown below.
I would like to change the property of a text box to Visible and Required when a user chooses a specific value in a drop down box of a different text box. Is this possible using a macro or some other way?
I have a report that is looking up min and max quality specifications for a product. The table with the specifications has a Yes/No check box field for when the specs are defined as "Internal". So the table looks like Product/MinValue/MaxValue/Internal
I want the report to display "TEST AND RECORD" when the Internal box is checked where the MinValue and MaxValue (that are stored in the table) would be displayed otherwise.
This forum has been so useful to me so far... but having searched through a load of topics for a few hours now I just cannot find a correct method of having the ability to update two tables from a form.
I firstly created a query that selected the nessary fields I wish to update from the two tables.
And its apparent that I need to use RecordSet to insert the information from the form into the query.
I have found a few different ways of doing this - none of which work for me :( HELP!
Method 1 Private Sub Save_Record_Click() 'Save all entered information to tblprocess request and tblBackupRequest
Dim db As DAO.Database Dim rs As DAO.Recordset
messageusr = MsgBox("Save this infomation?", vbYesNo + vbExclamation, "Warning you are about to Save this information") If messageusr = vbYes Then
Set db = CurrentDb() Set rs = db.OpenRecordset("SELECT .Server, [Backup Request].Location, [Backup Request].BackupType, [Process General].Type, [Backup Request].[Size(GB)], [Process General].Group, [Process General].[Date required by], [Process General].[Requested by], [Process General].[Date/Time of request], [Process General].Notes FROM [Process General] INNER JOIN [Backup Request] ON [Process General].ProcessID = [Backup Request].ProcessID;")
'Clear fields on form to indicate write has occurred txtserver = "" cmblocation = "" cmbtype = "" cmbsize = "" cmbassign = "" txtrequiredby = "" txtrequestedby = "" txtrequest = "" txtnotes = ""
'Close recordset and database saverecord.Close db.Close MsgBox "This information has been succesfully saved" End 'return user back to form End If
End Sub
and I have even looked into an insert sql statement [B]Method 3 Dim SQL_Text As String SQL_Text = "INSERT INTO Backup Request (Server, Location, Type , Size(GB)) VALUES ('#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#','#Backup Request.txtserver#') &" INSERT INTO Process General (Group, Date required by, Requested by, Date/Time of request, General_Type, Notes;" Docmd.RunSQL (SQL_Text, false)
Method 1 seems to be popular but its returning the message Run time error '3061' Too few parameters. Expected 3. :confused:
I have a form I would like to validate input on. The field UpdatedResponsibilityCode can be updated but it can only have certain values based on the PaymentResponsibilityCode. Is there a way to force this validation?
If UpdatedResponsibilityCode = 4 then PaymentResponsibilityID can not equal 1
Can you have a form that will make a cosmetic change base on the value of a field?
So I have a Member's Detail form and I would like the form to look a little different (say header background a different color) based on the member type. Is this possible?
So I have a combobox that pulls values from a table. I am being requested to do an update to the database which means that I have to filter some values on that combobox based on a field on a previous form.For example:The main form is called "frmcontacts" and has a table field called "txttypeofcustomer" which can have two values: "customer" or "dealer".
The form that contains the combobox is called "frmnewissue" the combobox is called "cmbissuetype". "frmNewIssue" is opened through a button on "frmContacts". This combobox pulls the values from a table "tblissuetype". The values in that combobox are then: "Repair" "Return" "Question" "Replace".
If the value on txtTypeofCustomer is "Dealer" then all the values for the combobox remain visible. If the value of txtTypeOfCustomer is "Customer" then "Replace" cannot be visible or has to be greyed out.
I seem to need some help! I have a table with customer information in it ie: name, vehicle, (Key=Id number)... it is the master link to the repair table ie: mileage, (Key=ROnumber), and repair data.. these two work together beautifull now I need to add another table, a check sheet for checking over a vehicle. I create the table and the form however I cannot open a new form with the ROnumber from the second already open form into the newly open form. (the second form is based on a Query, and I have tried changing it to a SQL statement both with no luck) is there an example of what I need to be doing to link the ROnumber to the ROnumber in the new form, or create the ROnumber so it will add the data in the linked table? I need this to open using a button on the open repair form David
Have two tables: Assignment and StudentHeader - they are related by AssignmentGUID
Have the SQL:
SELECT StudentHeader.[Student ID], StudentHeader.GUID FROM Assignment INNER JOIN StudentHeader ON Assignment.GUID = StudentHeader.[Assignment GUID] WHERE (((Assignment.[Assignment Type])="Q") AND ((Assignment.[Assignment Number])=2)) GROUP BY StudentHeader.[Student ID], StudentHeader.GUID ORDER BY StudentHeader.[Student ID], StudentHeader.GUID;
I am new to MS Access, and am not sure if what I want to do is even possible. It is my understanding that comparing subsequent cells within a field in a database cannot be done- so I thought I'd see if there is a way to go around it.
I have a dataset for pedestrian activity, with over 3 million rows and 40 columns - too big for excel to handle.
I need to sort the entire dataset by 2 fields, following which I need to search down the field containing my pedestrian ids (numbered 1, 2, 3... till approx 10000), and when my ped id changes from one to the next, I need to check the value in a field showing the ped location, and if that matches with the ped's previous location, I need to copy out a cell corresponding to the previous cell's time stamp. If it doesn't match, I need to copy out another time stamp from another field.
In access report, I'm trying to change color of text in specific records based on the true/false value in another record. Works in forms using conditional formatting, but won't seem to work in a report.
Here's what works in forms : IIf([2009 Symposium]=true, forecolor=255 ....this changes the records to red.
But using the same expression in a report doesn't change the text color.
My DB has many (!) forms that are based on Crosstabs queries. The headers are years, and therefore my formfields has controlsources like 2014, 2015 and so on.
Example:
The table it comes from looks something like this:
The crosstab then takes the EndYear as a coloumn header. Then my forms use these fields, now named 2014, 2015, 2016 .... and give me a nice looking table showing the accumulated "end" for multiple projects.
The problem is when the year changes after 31/12, all fields must manually be changed. This means I have to manually change 2014 to 2015, 2015 to 2016 and so on in all of my formsfields controllsource.
I have played around to find a way to change the source of the field, and hoped I could say something like =year(now()) and for the next year say =year(now())+1 but this only returns that year as a value, and not as a controlsource...
The query functions nicely (ive changed the system time, and it has no issues with this structure), but the forms source is still in the previous year, and returns and error (of course).
When the year changes past new years eve, so will the headers (fieldname) change... but how to make the form change fieldsource?
from a table with fields userID and Date (in which any userID has multiple records with different Date values, and other fields) i have built a select query based on Date = one specific value. can anyone give an explanation (for beginners), through a sample code, that expands the query recordset so that the new recordset includes all records per userID that qualified in the prior recordset? below is an example. thank you!
Table with records: userID / Date u1 / d1 u1 / d2 u2 / d1 u2 / d2 u3 / d2 ... 1st Select Query (where Date = d1): u1 / d1 u2 / d1
Desired 2nd Query based off 1st Query (where ?): u1 / d1 u1 / d2 u2 / d1 u2 / d2
I am trying to come up with a simple way for users to query the database and pull back entries relating to the (potentially vague) data they input.
I know how to run a query based upon data input in a form ("=[Forms]![FormName]![FieldName]"), and I know I can do this several times in one query.
However, if one of the fields is blank, the query doesn't return anything.
Is there a way to use only one query, but still allow the user to leave search criteria blank? The only thing I can think of is by using a very convoluted VB script and creating a query for each possible combination of criteria.
It's not an error-checking issue, either, as entering info into all of the fields isn't required.
For reference, the searchable fields are: Agent Name, Beginning Date, Ending Date (intended for a date range), Request Type, and District Name.
A user could then search for any requests made by an Agent; any requests by an agent within a date range; any requests made by anyone in a date range; ad nauseum.
There are quite a few combinations, there, and I'd rather not create a query for each one (or code the VB conditionals to choose the right one).
I hope I'm clear in what I'm trying to accomplish :o
I want Access to automatically generate a reference number for a record based on the values in on two other fields for a given record using a form.
The first field is called Operation Number.
The second is Bag Number.
The reference number needs to be in this format: 19C.3.1
Where 19C is the Operation Number, 3 is the bag number, and 1 is automatically generated. Additionally I need the last number --the automatically generated one--to go back to 1 if with each new bag number.
This is kind of like library catalog numbers. Not sure how to do this.
I have a form where customer data is entered. one field serves as a criteria for a query. If, for example, A is selected in the form the query uses the criteria "A" in the X field f query. However, if B is selected in the form, the query uses the criteria "B" in Y field of query.
I can easily do this by setting up two queries and having criteria A run in X field of query and similarly for B. However, it seems as if there should be a way for me to have one query and simply use the criteria in a different field.So, is there VBA that will update a given query's SQL to use one criteria in one case and another critiera in another case?
What is the easiest way to get the results from a query? I now create an empty form and set the sql-statement as the recordsource, but this means I can't open two forms simultaniously because they use the same subform. I know how to creat an ado-recordset, but this would mean the client needs to install extra dll's, my client doesn't have those (I want the program to work on a pc that has access installed and nothing more).
There must be an easier way...
Another problem I have is that when access is closed the MSACCESS proces keeps running, is this a bug, can I have caused this?
With any other access file this is not the case, but they do not have an autoexec.
I have a dashboard-style form that has a list box with tasks connected to a query with line items of those tasks.
I have another query that will only show the line item selected from the list box. This means the query will only have one line item.
I want to have a command button below the list box that, when clicked, will modify the solo item in the second query to change a yes/no field from no to yes.
How can I accomplish this? Also, how can I make a button that deletes the selected line item?
I will need to create a new column in a query with data based off of two columns currently in my DB.
Current fields are: [TestType] & [TestReason]. New TEMP field will be [TestCombo]
TestType has 4 possible option via drop down. TestReason has 21 possible options via drop down. TestCombo will be one of 10 options depending on the data in TestType & TestReason.
examples: (here is an example of how the combo field will be populated. There will be ten total rules like this. one for each possible text option in the combo box.) If [TestType] is "DRUG" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTDT".
If [TestType] is "ALCOHOL" & [TestReason] is "PRE" or "RAND" or "PA" then [TestCombo] would be "DOTAT".
If [TestType] is "DRUG" & [TestReason] is "N-PRE" or "N-OTHER" or "N-FUP" then [TestCombo] would be "NDDT".
The data does not need to be saved anywhere at all in the database, just generated when pulling this query. It will then be exported and dumped into another system.
Thanks. Here is a temp copy of the database with some junk data to maybe make it a little more clear.
I am working on a database that tracks products. The products are divided into groups (Bath Salt, Stick Incense, Candle, etc.) and these are store in a table. The products themselves are stored in another table.
When a new scent is created, it can belong to multiple groups ... for instance, Strwberry Vanilla could be a perfume, a bath salt, a candle, stick incense.
When the form is opened, I want to read the contents of the groups table and create a check box for each entry.
I do not really want to use a list/combo box for this task ... would prefer to be able to check in a list of boxes to specify what groups to create entries for when a new product is entered.