Gettin records from VB code

[Isis]

I am currently using something like

Me.Controls(strFieldName).SetFocus
strT = Me.Controls(strFieldName).Text

to get fields from a currently open form with the fields displayed.

Is there a way of getting at and manipulating field data from various
Tables from within my VB code in Access ? Something like the following;

CUSID = 'JONES1'
Get(CustomerTable)
strPCode = CUSPOSTCODE

So locating a particular record based on a unique key value and then
using the various field values to do other things ? One of the problems I
have is that I want info from various different Tables and I cannot use
my first method if fields are hidden on the Form.

Some pointers would be great if possible

Thanks for any help

 

[Albert D.Kallal]

I am currently using something like

Me.Controls(strFieldName).SetFocus
strT = Me.Controls(strFieldName).Text

The above is wrong. You DO NOT WANT to use setfocue for JUST reading a
controls value. When you do the above, all kinds of things can happen.....

Simply use

me.Fieldname

or

me.Fieldname.value (value is the default property..so, it is
optional)

the .text property is a SPECIAL AND RARE used property of the control that
is ONLY used for while the control has the focus. When you use the .value
property, the you do NOT have to change the focus.

msgbox "last name field on form is " & me.LastName
msgbox "first name field on forms is " & me.FirstName

Note how the above we do not change the focus. So, drop that use of focus,
it is extra code. It is code that forces the cursor to change focus JUST to
read, or set a silly value....no need to do this....
(note: in VB6, they always used the .text, and in ms-access, the equivalnat
is the .value. We have BOTH propeites abvialing..but the .text is speical in
ms-access....and, if you are comming from a VB envirment...use .value, not
..text).

Is there a way of getting at and manipulating field data from various
Tables from within my VB code in Access ? Something like the following;

CUSID = 'JONES1'
Get(CustomerTable)
strPCode = CUSPOSTCODE

Yes, there is a number of way to grab data from tables. Which approach you
use will depend on what you need to do..

The most easy function to use is called dlookup()

The dlookup() function is as follows

dlookup("field name", "table name", "where condition"

So, to grab a value of lastname of of a customer reocrd with a id of 123, we
would use

dim strLastName as string

strLastName = dlookup("LastName","tblCustomers","id = 123")

or, we could prmpt for the id number

dim strLastName as string
dim strID as string

strID = inputbox("What customer to see name of")

strLastName = dlookup("LastName","tblCustomers","id = " & strID)

However, if we needed the first name, and phone number, then It would be
rather expensive to query the database over and over for each field. So, if
you need several values, the we would use a reocrdset. Note that reocrdets
are also used for processing MORE THEN one record of data in code.

dim rstCustomer as dao.recordset
dim strSql as string

strSql = "select * from tblCustomer where id = 123"
set rstCustomer = currentdb.openreocrdset(strSql)

At this point, we can use all values from the reocrd

rstCustomer!LastName
rstCustomer!Firstname

etc. etc....

or, to process many reocrds...

strSql = "select * from tblCustomer where City = 'Edmonton' "
set rstCustomer = currentdb.openreocrdset(strSql)

do while rstCustomer.EOF = false
msgbox "about to process customer = " & rstCustomer!CompanyName
' your processing code goes here....

rstCustomer.movenext
loop

rstCustomer.Close
set rstCustomer = nothing

I would suggest you get yourself a book that has some coding examples....

 

[Isis]

The above is wrong. You DO NOT WANT to use setfocue for JUST reading a
controls value. When you do the above, all kinds of things can
happen.....

Simply use

me.Fieldname

or

me.Fieldname.value (value is the default property..so, it is
optional)

the .text property is a SPECIAL AND RARE used property of the control
that is ONLY used for while the control has the focus. When you use
the .value property, the you do NOT have to change the focus.

msgbox "last name field on form is " & me.LastName
msgbox "first name field on forms is " & me.FirstName

Note how the above we do not change the focus. So, drop that use of
focus, it is extra code. It is code that forces the cursor to change
focus JUST to read, or set a silly value....no need to do this....
(note: in VB6, they always used the .text, and in ms-access, the
equivalnat is the .value. We have BOTH propeites abvialing..but the
.text is speical in ms-access....and, if you are comming from a VB
envirment...use .value, not .text).



Yes, there is a number of way to grab data from tables. Which approach
you use will depend on what you need to do..

The most easy function to use is called dlookup()

The dlookup() function is as follows

dlookup("field name", "table name", "where condition"

So, to grab a value of lastname of of a customer reocrd with a id of
123, we would use

dim strLastName as string

strLastName = dlookup("LastName","tblCustomers","id = 123")

or, we could prmpt for the id number

dim strLastName as string
dim strID as string

strID = inputbox("What customer to see name of")

strLastName = dlookup("LastName","tblCustomers","id = " & strID)

However, if we needed the first name, and phone number, then It would
be rather expensive to query the database over and over for each
field. So, if you need several values, the we would use a reocrdset.
Note that reocrdets are also used for processing MORE THEN one record
of data in code.

dim rstCustomer as dao.recordset
dim strSql as string

strSql = "select * from tblCustomer where id = 123"
set rstCustomer = currentdb.openreocrdset(strSql)

At this point, we can use all values from the reocrd

rstCustomer!LastName
rstCustomer!Firstname

etc. etc....

or, to process many reocrds...

strSql = "select * from tblCustomer where City = 'Edmonton' "
set rstCustomer = currentdb.openreocrdset(strSql)

do while rstCustomer.EOF = false
msgbox "about to process customer = " & rstCustomer!CompanyName
' your processing code goes here....

rstCustomer.movenext
loop

rstCustomer.Close
set rstCustomer = nothing

I would suggest you get yourself a book that has some coding
examples....

Albert - this stuff is just what I wanted - I am familiar with several
other DB languages and just needed the info you supplied. I think will
solve all my issues with this matter and I am very obliged for your help.
Just to clarify, my original code was for the manipulation of a template
and fields and the only way I could find to combine them was the screen
scrapping method, but I think your help will enable me to rewrite this
more sensibly.

As a seperate point - my templates rely on a function that reads through
the template text and attempts to find out if a word in the template
exists as a field in the database - it then substitutes the contents of
the field for the field marker - the line;
Me.Controls(strFieldName).SetFocus
attempts to find out if a field exists with the label strFieldName (that
I have prefilled with the name from the template file) - are there any
pointers you can give with regards to methods of Record/Field reading you
have given me ?


Thank you very much for the enlightnment.

Regards

 

[Albert D.Kallal]

I have prefilled with the name from the template file) - are there any

pointers you can give with regards to methods of Record/Field reading you
have given me ?

Hum, you can change what the datasouce of a control will point to on a form
at runtime, but you DO NOT want to try and modify the number of controls, or
add new controls to the form at runtime...it is not a workable approach...

So, it is not clear if you just need to ref a control on a form, and change
what the underlying field that the control points to? (which field it will
point to is going to be limited to the particular set of fields that the
forms datasouce is set to).

For the most part, you should not need to define what control is attached to
what field of a table at runtime. You *can* do this, but it is not needed
very often.....

I might not be understanding what you ask above......

 

[Albert D.Kallal]

one more thing...by templates...we talking about access...and not
word...right?

 

[Isis]

Hum, you can change what the datasouce of a control will point to on a
form at runtime, but you DO NOT want to try and modify the number of
controls, or add new controls to the form at runtime...it is not a
workable approach...

So, it is not clear if you just need to ref a control on a form, and
change what the underlying field that the control points to? (which
field it will point to is going to be limited to the particular set of
fields that the forms datasouce is set to).

For the most part, you should not need to define what control is
attached to what field of a table at runtime. You *can* do this, but
it is not needed very often.....

I might not be understanding what you ask above......

Thanks again Albert - no my templates are assembled from text files
containing HTML and other languages and manipulated by the VB using
Access DB to hold the various options etc.

At present I put placeholders into my text files which are the names of
fields within Access Tables - then I parse the text files within VB code
- when the function finds a placeholder that it thinks may be a field
name I need to check if that fieldname actually exists within the DB - I
can do this at the moment by having all the fields I need to use showing
on a form and checking whether the placeholder name generates an error
using this code;
Me.Controls(strFieldName).SetFocus ' Can't get text property if field
does not have focus

strT = strT + Me.Controls(strFieldName).Text
If Err.Number <> 0 Then ' Can NOT get field data
strT = strT + "<<ERROR: Problem reading field '" + strFieldName + "'>>"
Err.Clear
End If

As you have now given me the means to access the fields without using a
form I would like to check whether the placeholder name that I think MAY
be a field name actually is OR just looks like one - in pseudo code
something like;

If dlookup("CUSLAST", "Customers") = ERROR() [No Such Field]
then do not do my normal processing
else
strSTRING = dlookup("CUSLAST","CUSTOMERS)
endif

I know this is not VB - but just to give an idea of what I am trying to
achieve - so to parphrase I will have a possible field name - [CUSLAST]
and I need to check if that is a field in my database - if ti is I will
do something with it, if not I will continue parsing the text file.

I really appreciate you taking the time to help me out with understanding
this Albert, Thank You.

Regards

 

[Albert D.Kallal]

Hum, you can change what the datasouce of a control will point to on a
form at runtime, but you DO NOT want to try and modify the number of
controls, or add new controls to the form at runtime...it is not a
workable approach...

So, it is not clear if you just need to ref a control on a form, and
change what the underlying field that the control points to? (which
field it will point to is going to be limited to the particular set of
fields that the forms datasouce is set to).

For the most part, you should not need to define what control is
attached to what field of a table at runtime. You *can* do this, but
it is not needed very often.....

I might not be understanding what you ask above......

Thanks again Albert - no my templates are assembled from text files
containing HTML and other languages and manipulated by the VB using
Access DB to hold the various options etc.

At present I put placeholders into my text files which are the names of
fields within Access Tables - then I parse the text files within VB code
- when the function finds a placeholder that it thinks may be a field
name I need to check if that fieldname actually exists within the DB - I
can do this at the moment by having all the fields I need to use showing
on a form and checking whether the placeholder name generates an error
using this code;
Me.Controls(strFieldName).SetFocus ' Can't get text property if field
does not have focus

strT = strT + Me.Controls(strFieldName).Text
If Err.Number <> 0 Then ' Can NOT get field data
strT = strT + "<<ERROR: Problem reading field '" + strFieldName + "'>>"
Err.Clear
End If

As you have now given me the means to access the fields without using a
form I would like to check whether the placeholder name that I think MAY
be a field name actually is OR just looks like one - in pseudo code
something like;

If dlookup("CUSLAST", "Customers") = ERROR() [No Such Field]
then do not do my normal processing
else
strSTRING = dlookup("CUSLAST","CUSTOMERS)
endif

I know this is not VB - but just to give an idea of what I am trying to
achieve - so to parphrase I will have a possible field name - [CUSLAST]
and I need to check if that is a field in my database - if ti is I will
do something with it, if not I will continue parsing the text file.

I really appreciate you taking the time to help me out with understanding
this Albert, Thank You.

Regards

 

[Albert D.Kallal]

sorry about the bumped send for the last message...

Just build a function that you pass the table name, and the field name....


if chkField("CUSLAST","tblCustomers") = true then
strSTRING = dlookup("CUSLAST","CUSTOMERS)
endif

Of course, in the above..what record is dlookup going to retrieve here?
I suspect some type of reocrdset should be used here, as using dlookup
without the "where" clause don't make sense..

Anyway, here is the function that could check for a field...just place the
following code in a standard module..and you can use it everywhere...

Public Function chkField(strField As String, strTable As String) As Boolean

Dim f As DAO.Field
Dim db As DAO.Database

Set db = CurrentDb
For Each f In db.TableDefs(strTable).Fields
If f.Name = strField Then
chkField = True
Exit For
End If
Next f

end function