Modules & VBA :: Generating Text Fields From Query
May 17, 2015
I've come across a problem that doesn't make sense to me. I have a form with a combo box on it. Each time the combo box is used or the form is moved to another record it triggers an event to update the text boxes.
I get the information for the text boxes from a query. Everything was working smoothly until I decided to add a text box to the form to be filled in. My thought was I add the column from the query to the code and it will update. No dice. It doesn't recognize any information in the query. But when I run the query as a standalone it sees the text. When i open the table that holds the text, it is still there.
Code:
Private Sub cboDoctor_AfterUpdate()
'update doctor fields on the update of the combo box
If Len(Me.cboDoctor) > 0 Then
Call DoctorName_Change
[code]...
When i step through the code it shows values for each of the columns except for column 11. It reports as 'null.' Things I've tried:
-Updating the combo box by reselecting the value for the record.
-Restarting DB.
-Changing from one record in the combo box back to the original record.
Is there a limit of columns that can be used? Column 10 works just fine...
I have a library function that will allow the user to nominate a query (as one of its arguments) in the calling application which must have an email field. The function will then Do Loop the email field, concatenating it before creating an email and addressing it. The intended functionality is that a developer can easily create a group email, just by creating a query.
This works fine if the query is filtered "statically" - i.e. I specify which group of people by typing in their "Site_ID" in the criteria. However I want developers to be able to creating dynamically filtered queries (perhaps by the group's ID on a calling form). Within the query (to test it), the filter is therefore [Forms]![Test Function Calls]![Site_id]. When I run the code, I am then presented with "Run-time error 3061: Too few parameters. Expected 1". The code in question is:
Dim rst As DAO.Recordset Dim stTo As String 'one of the function's arguments received from the calling function. Dim stToString As String 'the built up concatenated emails
Set rst = CurrentDb.OpenRecordset(stTo, dbOpenDynaset, dbSeeChanges)
I have a table with one field set as a hyperlink. I have to generate the hyperlink according to what else is going on in the record, I do this by assembling a text string which I can write to a text field in the same record. I have no problem doing this but when I try to copy that string into the Hyperlink field I get type mismatch. Incidentally if I put two text boxes on a form and bind one to the text and one to the hyperlink field the I can 'cut and paste' it OK so how come I can't do it with Access basic?
Dim stGunlib As String Dim stGunlib2 As String stGunlib2 = [Stock number] stGunlib = "www.shooting.uk.com/Gunlibrary/" + stGunlib2 + ".jpg" [Link] = stGunlib [Picture] = stGunlib ([Picture] is a hyperlink field) it goes wrong here
I am trying to create a database to manage some project related functions. the main thing I want it to do is manage the resource allocation and costs related to fixed price projects and ultimately be able to produce reports that show the Bid vs Forecast vs Actual in relation to someones time and hence cost.in building the first table which I want to store the initial Bid data. I have a need to have fields automatically generate based on the value of two other fields. this is used to identify how long the project will run for.
for example: prject start date of 1/1/2014 project end date of 31/12/2014
Auto Generate 12 fields in which to put the monthly resource usage for each resource.
I have a report that is purely for points gained from products. Each product is split into 3 (e.g. Investment A, B + C) and there is a indicator that means the points gained can be added up (e.g. I for Investment and S for Savings).
I have made the report which is many unbound fields. There is a summary section for all points added together with fields Total Amount, Monthly Target, Quarterly Amount and Quarterly Target.
Then the report is split into each of the 4 products (Each of these have different amounts of points. Number indicates it):
Investments (10), Savings (15), Pensions (15) and Protection of Life Savings (20).
Each of these have 4 fields - Total Amount, Target Amount, Quarterly Amount and Quarterly Target.
I need a way to generate these point scores using the Product_Points field. I already have Quarterly Target and Target Amount because there is a set field called Points_Annual_Target.
EDIT: I also don't want it to come up with the 'insert parameters' so all the info is one page.
I need to generate quotation numbers for the quotes that I will be populating from my database. The process now is simply the format of date-n. The date is in standard format being "05222015" and the n is simply the number quote generated on that given date, For example the first quote generated today would be 05222015-1. Best way to generate these numbers automatically.
Is is possible to generate a workbook for each record in a recordset, and title it using the unique identifier for that record?
I created the following code, but it does not seem to work. First of all it doesnt like the string and secondly it does not like the declaration of wb as Excel.Application
Code: Private Sub generate_wkbk() Dim rsID As DAO.Recordset Set rsID = CurrentDb.OpenRecordset("Select * FROM tblMeeeting;", dbOpenDynaset) With rsID rsID.MoveFirst
On a form, I want to disable the save button, 'cmdSave' whilst the form's mandatory fields have been left blank.
I've put in a smart tag, called, 'Req' against each required field and have used the following code on the forms current event.
Code: Private Sub Form_Current() Dim ctl As Control For Each ctl In Me.Controls If ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox Then
[Code] ....
The save button is disabled, but it won't enable again after each field has data entered against it.
I also have this code in the AfterUpdate event in each required field:
Code: Private Sub cboErrorID_AfterUpdate() Call Form_Current End Sub
How to prevent duplicates on the combination of two fields - text & numeric?
I'm currently using the code below that warns users when the combination of two fields have already been used. (Combination of the TWO fields has to always be unique so if used again will warn the user)
Works well when both fields are numeric but fails when the JobDetails field is changed to text in the main table (tblPPMPLanner)
Code: Option Compare Database Option Explicit Private Function IsDuplicateRecord() As Boolean On Error Resume Next Dim PreviousRecordID As Long IsDuplicateRecord = False
[Code] ....
The field that should be a text field is called "JobDetails"
I have two tables in a query joined by a ID field. The problem is in the one table the ID fiels is a text format and in the other table its a number format so the query builder doesnt like that and gives me the Type mismatch in expression error.
There's no way around it though; i need it to be those formats in its respective tables for reasons i wont go into here for simplicity.
I have an on click event to mail a report which works. I want to change the text to include data from a table.
I changed the code to include the field 'office' from the table 'Checks' but get an error saying 'Object required'.
Code is : Private Sub cmd_mailreport_Click() Dim office As Object Set office = Checks.office DoCmd.SendObject acReport, "checks", "PDFFormat(*.pdf)", _ "info@company.com", "", "", office & " Daily Check - " & Date, "Attached is the report for the office", _ True, "" End Sub
I want to know how to generate a result from a query in a control of a form? For exampel I have made a SQL count function in a query and I want to use this result i a control of a form.
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause FROM tblMainTWTTPSheet GROUP BY tblMainTWTTPSheet.txtRootCause HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null)) ORDER BY Count(tblMainTWTTPSheet.txtRootCause) DESC;
But What I am trying to do is on a form select dates and generate data in this query. which doesnot happen even if I use the following query.
SELECT tblMainTWTTPSheet.txtRootCause, Count(tblMainTWTTPSheet.txtRootCause) AS CountOftxtRootCause FROM tblMainTWTTPSheet WHERE (((tblMainTWTTPSheet.date) Between [Forms]![frmStratificationOfRootCauses]![startDate] And [Forms]![frmStratificationOfRootCauses]![endDate])) GROUP BY tblMainTWTTPSheet.txtRootCause HAVING (((tblMainTWTTPSheet.txtRootCause) Is Not Null) AND ((Count(tblMainTWTTPSheet.txtRootCause)) Is Not Null));
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
I am trying to run simple query make a chart in report through this query but its not generating chart?
SELECT [Unsafe Act Unsafe Condition].[Inspection Audit], Count([Unsafe Act Unsafe Condition].[Inspection Audit]) AS [CountOfInspection Audit] FROM [Unsafe Act Unsafe Condition] WHERE ((([Unsafe Act Unsafe Condition].Date) Between [Forms]![frmSafetyReportOut]![startDate] And [Forms]![frmSafetyReportOut]![endDate])) OR ((([Unsafe Act Unsafe Condition].Department)=[Forms]![frmSafetyReportOut]![cboDepartment])) GROUP BY [Unsafe Act Unsafe Condition].[Inspection Audit] HAVING ((([Unsafe Act Unsafe Condition].[Inspection Audit]) Is Not Null) AND ((Count([Unsafe Act Unsafe Condition].[Inspection Audit])) Is Not Null));
Hi guys, I am trying to create a query collecting data from 4 tables as well as calculating totals delivered but it's hurting my head trying to work out all the joins can anyone help please?
The relationships are pretty straight forward, OrderNumber on Orders and OrderDetails are linked as are DeliveryNoteID on Deliveries and Delivery Details.
I want the query to list every single item for all orders as well calculating how the quantity remaining to be delivered based on deliveries so far and Qty ordered.
So I want to create a report that pretty much just displays:
(This is a modified repost - which hopefully makes sense) I am using Access2003. I am trying to set up a fast method of creating a union query. I have a jobs table that stores info about jobs with a separate table for each job that pulls together info from elsewhere when a review is conducted. The tables are as follows (and are linked from a paradox DB) :-
Table Name: jobs JobID (J000001, J000002, etc) Status (Live, Filled, etc)
Review Tables
Table Name: J000001 / J000002 etc Consultant: (Joe, Terry etc) ObjectID: (RoberI, SmithJ etc) Status: (H, P, D, R etc)
The jobs table contains information about jobs, including a unique code (JobID) that identifies the job. There is also a status filed that tells us whether the job is Live or closed etc.
The first time a job is reviewed a new table, a review table is generated, and the name of the table is the same as the JobID for that job. So Job J000001 has a review table with table name J000001 etc. The review tables may contain information with the same ObjectID (as they are unique fields from a third table – the candidates table)
I would like to generate a union query for all jobs in table jobs with a status of live. I can do this manually, if I review a list of live jobs, with the following sql expression;
SELECT ObjectID, Consultant, Status, "J000001" as [JobNo] FROM J000001 UNION SELECT ObjectID, Consultant, Status, "J000002" as [JobNo] FROM J000002 UNION SELECT ObjectID, Consultant, Status, "J000003" as [JobNo] FROM J000003;
I can then append the info into a new table. However this query is run at least twice a day and things change.
I would like to know is there a means of automatically generating sql for the union query based on results of a query of the jobs table ?
I have a database with two fields [RECEIVED] and [DISPATCHED] which unfortunately are text fields which I can't change (linked via ODBC). Time is entered in these fields as military time.
I created a query with [DISPATCHED]-[RECEIVED] which works unless the time spans different hours. Example 1605-1555 returns 50 instead of the correct 10.
Any idea how I can use a query to calculate this accurately. I am an intermediate user. Any help appreciated.
I am trying to create a database to link to an online auction site (not ebay) and I want to create a query that randomly generates auction end time and auction length for all listings. I have tried int(rnd()) and various versions but can only generate the same number for all fields in the query.
I am using a update query to update a table from another table. This query however will works only if I set the allowzerolength to yes in the table design otherwise validation error message will appear. This means that I cannot use the "Is Null " anymore because this empty space " " can appear. Is there any a way i can filter out those empty fields in the query design then? Thank you for your help.
I have created some queries that are joined on long text fields (80-120 characters). I can save the query in the graphic query design window. But when I reopen the window I get messages that the Joins have been deleted. I cannot find any references to this issue. Does anyone have experience or info on this?
I don't need a lecture about the use of long text fields in Query Joins. And yes I have used Search first.
I'm trying to concatenate two text fields into a memo field using an expression in a select query. My problem is that the text fields together end up more than 255 characters, so I need the resulting field to be a memo instead. I can't change the underlying text fields to memo fields because this is a large database used by others who need those fields to be text.
I am just querying a single table, no relationship involved with another table. As you can see form the attached jpeg, the ZIP field in some cases is empty. I would run a search using Is NULL but the field is NOT numerical. It's a long story but I had to make this field a TEXT field. Basically, what statement do I have to insert in the criteria field to just pull up the EMPTY ZIP fields?
