Re: table field in vba
- From: lyle fairfield <lyle.fairfield@xxxxxxxxx>
- Date: Fri, 31 Jul 2009 09:14:50 -0700 (PDT)
It's very easy to make syntax errors here in the newsgroup because
there is nothing that checks up on us, as there is in the vba editor.
I'm 99.44% Tony meant one of these consturctions:
Sub temp()
Dim r As DAO.Recordset
Set r = DBEngine(0)(0).OpenRecordset("Customers")
Debug.Print r("Last Name")
Debug.Print r![Last Name]
Debug.Print r(2)
End Sub
If you're doing a whole bunch of these things declaring field
variables is faster.
Sub temp2()
Dim r As DAO.Recordset
Dim f As DAO.Field
Set r = DBEngine(0)(0).OpenRecordset("Customers")
Set f = r.Fields("Last Name")
Debug.Print f.Value
End Sub
<ramble>
I never use !; it's archaic and inefficient;
I use a persistent recordset every five years or so; they're archaic
and inefficient, not to mention dangerous;
If I were doing this I would probably use SQL;
It's unlikely that I would do this as updating one table from another
is redundant;
JET accepts long field names with grace so acronyms and abbreviations
are unnecessary.
</ramble>
Be like Nancy; just say "No" to DAO recordsets.
--
Lyle Fairfield
Age: 71
Years Spent Waiting for Windows to Install Updates and Shut Down My
Computer: 35.5
On Jul 31, 11:07 am, bobh <vulca...@xxxxxxxxx> wrote:
On Jul 29, 4:02 pm, "Tony Toews [MVP]" <tto...@xxxxxxxxxxxxxxx> wrote:
bobh <vulca...@xxxxxxxxx> wrote:
In AccessXP I have a table and in that table I have several fields
with names like FLPR-Q1 thru FLPR-Q10
when I write this in vba code to loop thru these and update feilds in
another table vba interupt/format the name like this
MyRec!FLPR - Q1
which causes an error when the vba code runs.
Why are spaces being put before and after the " - " in the field
name?? I actually typed it as FLPR-Q1 and as soon as I press enter it
changes to FLPR - Q1
1) VBA thinks you are trying to subtract Q1 from FLPR. Put square brackets around
FLPR-Q1.
2) You can use syntax like MyRec!("FLPR-Q" & LoopCounter) to get from fields 1 - 10
where LoopCounter is the Dimmed variable in the Do loop.
3) Depending on what FLPR is you should consider putting FLPR in it's own table so
you aren't limited to ten entries.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
Granite Fleet Managerhttp://www.granitefleet.com/
I tried to setup my vba code as in tip#2 above this way
MyRec!("[FLPR-Q" & MyCnt & "]")
and I'm getting the following error, any ideas?
error = Compile Error - Type Declaration Character does not match
declared data type
I tried without the square-brackets and I get the same error,
MyRec is Dimmed as DAO.Recordset,
MyCnt is Dimmed as Interger and incurments 1 to 10
thanks
bobh.
.
- References:
- table field in vba
- From: bobh
- Re: table field in vba
- From: Tony Toews [MVP]
- Re: table field in vba
- From: bobh
- table field in vba
- Prev by Date: Re: table field in vba
- Next by Date: Re: Getting the First Record For A Particular Field
- Previous by thread: Re: table field in vba
- Next by thread: idea's
- Index(es):
Relevant Pages
|
Loading
