Solved

VBA - Getting Data From A Field

Posted on 2001-06-04
12
302 Views
Last Modified: 2011-04-14
...okay, sorry if this caused confusion.
What I am trying to do is make a temporary table to hold all of the information I need from certain records. I do not know how to achieve this through a query and someone suggested that using the table to retrieve the necessary info was a good idea.

What I then want to do is look at a certain field within this table to determine whether or not it contains any data. The outcome of this will decide which report is run.

The names of the fields do include underscores and I think I am using the right syntax to get at them but can't be sure. I cant be sure that Tables!barcodetemp!SB_EXPECTED_LENGTH is the correct way to reference the field.

Thanks again,
David.

*---------------------------------------------------------

Function chkexplength()

DoCmd.OpenQuery "barcode data table"

If Tables!barcodetemp!SB_EXPECTED_LENGTH Is Not Null Then
    DoCmd.OpenReport "BARCODE LABELS"
Else
    DoCmd.OpenReport "BARCODE LABELS TWO CODES"
End If

End Function
0
Comment
Question by:DSE
  • 5
  • 2
  • 2
  • +2
12 Comments
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
Hi DSE,

What is the use of maketemptable is in your code?

What exactly are you tring to achive?

More info will be much helpful for us in order to asist.

Does expected_end field is defined with underscore (_) in your table, or is it [expected end] (no underscore). if this is the case, then try:
Function chkexplength()

DoCmd.OpenQuery "maketemptable"

If Tables!mytemptable![expected end] Is Not Null Then
   DoCmd.OpenReport "BARCODE LABELS"
Else
   DoCmd.OpenReport "BARCODE LABELS TWO CODES"
End If

End Function

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
or rather try:

Function chkexplength()

DoCmd.OpenQuery "maketemptable"

If Not IsNull(Tables!mytemptable!expected_end) Then
   DoCmd.OpenReport "BARCODE LABELS"
Else
   DoCmd.OpenReport "BARCODE LABELS TWO CODES"
End If

End Function
0
 
LVL 1

Expert Comment

by:pscholl
Comment Utility
It seems to me that you need to open the "maketemptable" as a "dynaset" then run the if statement against the dynaset.  It looks like you are trying to run both an access query and vb at the same time.  The two don't mesh, this is why you get the need object. -> the object would be the Dynaset.  See the Help screens in access under Select Recordset/Dynaset for an example of using a query to open a dynaset.  hope this helps...
0
 

Expert Comment

by:mmeinz
Comment Utility
DSE,
There might be an easier solution to what you need. Try using the "Count" function. I've clipped out the MSAccess help file which will show how quickly you can determine if any records exist, and evaluate accordingly. It goes like this:

Calculates the number of records returned by a query.

Syntax

Count(expr)

The expr placeholder represents a string expression identifying the field that contains the data you want to count or an expression that performs a calculation using the data in the field. Operands in expr can include the name of a table field or function (which can be either intrinsic or user-defined but not other SQL aggregate functions). You can count any kind of data, including text.

Remarks

You can use Count to count the number of records in an underlying query. For example, you could use Count to count the number of orders shipped to a particular country.

Although expr can perform a calculation on a field, Count simply tallies the number of records. It doesn't matter what values are stored in the records.

The Count function doesn't count records that have Null fields unless expr is the asterisk (*) wildcard character. If you use an asterisk, Count calculates the total number of records, including those that contain Null fields. Count(
*) is considerably faster than Count([Column Name]). Don't enclose the asterisk in quotation marks (' '). The following example calculates the number of records in the Orders table:

SELECT Count(*)
AS TotalOrders FROM Orders;

If expr identifies multiple fields, the Count function counts a record only if at least one of the fields is not Null. If all of the specified fields are Null, the record isn't counted. Separate the field names with an ampersand (&). The following example shows how you can limit the count to records in which either ShippedDate or Freight isn't Null:

SELECT
Count('ShippedDate & Freight')
AS [Not Null] FROM Orders;

You can use Count in a query expression. You can also use this expression in the SQL property of a QueryDef object or when creating a Recordset object based on an SQL query.

Good luck,
mmeinz
0
 

Author Comment

by:DSE
Comment Utility
...thanks for the help so far, but I could do with some clarification.
We have a huge database that I am pulling records from and placing into a table for later use. After creating the table I need to be able to access a field within this table using VBA code.

What I need help with is the code to access this field within the table without opening the table for viewing and the using the field value to decide which report should be run.

Thanks and i hope this is easier to understand.
0
 
LVL 1

Expert Comment

by:Grunge
Comment Utility
perhaps some basic vba database tutorials might help... i cna't find any... anyone?..
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Expert Comment

by:Grunge
Comment Utility
perhaps some basic vba database tutorials might help... i cna't find any... anyone?..
0
 

Accepted Solution

by:
mmeinz earned 50 total points
Comment Utility
Hello DSE...try the sampling below for what I was trying to show you using the count function. This will allow you to check the table for records (as you indicated). If records exist then perform some operation, otherwise do something else. The code below will allow you to check whether you have viable records, or you can expand on the strCount to include special conditions by include a WHERE clause:

Dim strCount As String
Dim db As Database
Dim rc As Recordset

Set db = CurrentDb()
strCount = "SELECT Count([SP_EXPECTED_LENGTH]) AS SPNUM FROM BARCODETEMP ;"
Set rc = db.OpenRecordset(strCount)

If rc.EOF = True Then      'No Records Found
    DoCmd.OpenReport "NoRecordsReport"   'Do something
Else    'I've got records, Open this report
    DoCmd.OpenReport "GotRecords"
End If

P.S. Be careful...this limited size box did text wrapping on the SQL String above...either try putting everything on one single line or use the concatenation (sp?) value at the end of the string like this:
            AS SPNUM " & _
            "FROM BARCODETEMP ;"
I hope that made sense?

Best of luck
mmeinz
0
 

Author Comment

by:DSE
Comment Utility
That works great mmeinz...exactly what I needed...thanks everyone for all your help!
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
Hi DSE,

As commented above, you should use recordset to open the table (without using a form).

Say that the TempTabple that you are creating is maketemptable and the field to validate in this table is expected_end.

The method of reading the value of expected_end from maketemptable will look like the follows:

Dim DBs As Database
Dim RSt As Recordset
Dim strSQL As String

strSQL = "SELECT expected_end FROM maketemptable"
Set DBs = CurrentDB
Set RSt = DBs.OpenRecordset(strSQL)
'Loop maketemptable and print a deferent report for each record
'according to expected_end
While Not Rst.EOF
    If Not IsNull(RSt.expected_end) Then
        DoCmd.OpenReport "BARCODE LABELS"
    Else
       DoCmd.OpenReport "BARCODE LABELS TWO CODES"
    End If
Wend

'Release the variables
Rst.Close
Set DBs = Nothing

Hope this helps,

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
Hi DSE,

As commented above, you should use recordset to open the table (without using a form).

Say that the TempTabple that you are creating is maketemptable and the field to validate in this table is expected_end.

The method of reading the value of expected_end from maketemptable will look like the follows:

Dim DBs As Database
Dim RSt As Recordset
Dim strSQL As String

strSQL = "SELECT expected_end FROM maketemptable"
Set DBs = CurrentDB
Set RSt = DBs.OpenRecordset(strSQL)
'Loop maketemptable and print a deferent report for each record
'according to expected_end
While Not Rst.EOF
    If Not IsNull(RSt!expected_end) Then
        DoCmd.OpenReport "BARCODE LABELS"
    Else
       DoCmd.OpenReport "BARCODE LABELS TWO CODES"
    End If
Wend

'Release the variables
Rst.Close
Set DBs = Nothing

Hope this helps,

Nosterdamus
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
I'm slow today... Comment accepted as answer as I was typing my own....

Nosterdamus
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now