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...

View Replies


ADVERTISEMENT

Modules & VBA :: Query Parameters Generating Error 3061 With OpenRecordset

Sep 9, 2013

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)

[Code] ....

View 6 Replies View Related

Generating Hyperlink From Text In Access

Mar 3, 2007

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 using Access 2002

Thanks Dennis

View 6 Replies View Related

Generating A Field With Parts Of Other Fields

Jan 1, 2008

If I have three fields

Surname - text
Given name - text
Dateofbirth - dd/mm/yyyy

how can I generate a field consisting of

surname(then)first letter of given name (then) year of birth

(ie SMITHK66)

Thanks in advance

View 2 Replies View Related

Tables :: Auto Generating Fields

Jun 3, 2014

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.

View 3 Replies View Related

Reports :: Generating Report With Points Fields?

Apr 15, 2013

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.

View 1 Replies View Related

Modules & VBA :: Generating More Complicated Autonumbers?

May 22, 2015

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.

View 1 Replies View Related

Modules & VBA :: Generating Workbooks For Each Record In A Recordset?

Jul 18, 2013

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

[Code] .....

View 3 Replies View Related

Modules & VBA :: Use AfterUpdate To Calculate Age Using Two Text Fields

Apr 16, 2014

i want to use the AfterUpdate to calculate the age using two text fields

Private Sub Birthdate_AfterUpdate()
Me.AGE = DateDiff("yyyy", [Birthdate], Now()) + Int(Format(Now(), "mmdd") < Format([Birthdate], "mmdd"))
End Sub

but it wont work

View 4 Replies View Related

Modules & VBA :: Disable Button When Text / Combo Fields Are Null

Aug 5, 2013

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

View 13 Replies View Related

Modules & VBA :: Preventing Duplicates On Multiple Fields (Numeric And Text)

Oct 15, 2013

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"

View 5 Replies View Related

Modules & VBA :: Fields In Text And Number Format - Type Mismatch In Expression

Oct 29, 2013

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.

View 2 Replies View Related

Modules & VBA :: Selecting Fields - Change Text To Include Data From A Table

Nov 5, 2014

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

View 1 Replies View Related

Generating A Query Result

Feb 9, 2006

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.

View 1 Replies View Related

Query Generating Different Results

Jun 3, 2014

If I use this query I get the results what I want

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));

View 4 Replies View Related

Self Generating Query String Based On Query Results?

Jan 3, 2008

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:

05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD

etc...

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?

View 7 Replies View Related

Query Not Generating Chart In Report

Jul 3, 2014

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));

View 1 Replies View Related

Generating A Query Using 4 Tables And Totalling One Of The Columns :S

Feb 1, 2006

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 4 tables are as follows:

Orders
=====
OrderNumber
CustomerName
CustomersRef

OrderDetails
=========
Order_Items_ID
OrderNumber
ItemNumber
Qty
ItemType
Description

Deliveries
=======
DeliveryNoteID
OrderNumber
Date

DeliveryDetails
==========
DeliveryDetailID
DeliveryNoteID
ItemNumber
QtyDelivered

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:

Orders.OrderNumber - Orders.CustomerName - Orders.Customer_Ref -
OrdersDetails.ItemNumber - OrdersDetails.Qty - OrdersDetails.ItemType -
OrdersDetails.ItemDescription - Deliveries.DeliveryNoteID - Deliveries.Date -
DeliveryDetails.TotalQuantityDelivered

Can this be done as one query or do I need to join two queries together?

It's hurting my head :(

View 1 Replies View Related

Generating Reports Based On Dynamic Query

Mar 2, 2005

Hello All,

How can I create reports in Access based on dynamic queries? I did a lot of search on this but couldn't find anything reasonable.

Any help to get me started will be extremely appreciated.

Thanks

View 1 Replies View Related

Union Query - Generating From A Select Query ?

Jun 23, 2006

(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 ?

Any help greatly appreciated.

View 2 Replies View Related

Elapsed Time Query In Text Fields

Mar 24, 2007

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.

View 3 Replies View Related

Queries :: Generating Random Time From List In Query

Apr 29, 2015

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.

View 1 Replies View Related

Update Query Work On Allowzerolength On Text Fields

Sep 30, 2005

Hi,

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.

View 1 Replies View Related

Dropping Query Joins On Long Text Fields

Nov 14, 2005

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.

Thanks for your help.

View 3 Replies View Related

General :: Concatenate Two Text Fields Into A Memo Field In Query?

Sep 5, 2014

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.

View 3 Replies View Related

Queries :: Design A Query To Show Only Empty Field As TEXT Fields In Table

Mar 2, 2014

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?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved