Reference recordset field name with VBA

Does anyone know how to reference the field name through VBA code? For example the table the recordset (rstOrders) is based on has a field named Customer. How do I reference that in VBA?


Post your answer or comment

comments powered by Disqus
Is there a way to change a tables field names using vba?
I generate a table from a query that has field names of:
-11m,-10m,-9m,-8m,-7m,6m,-5m,-4m,-3m,-2m,-1m,0m
There is also a date field with an ending date say its 08/26/2003
I want the field names to be formatted as mm/yyyy with the 0m being 8/2003 -1m being 7/2003, -2m being 6/2003 and so on.
I tried using the dateadd function as the caption but it wouldn't accept it. Thats why I thought it might be better to change it in code after the table is generated.
Can someone help?
Thanks,
Scott

Hi all,

I have an Access DB with hundres of queries and reports, now I have to use an external DB (Oracle) and export data keeping the rest, no problem with that, I have added linked tables through odbc and works fine.

My problem is that some of the tables have field names with spaces, and Oracle doesn't admit them. Does anybody knows how to solve it without having to modify all the queries, etc?, I've been thinking about aliases or views over the linked table but I haven't found a way to create these.

Any help?

Thanks

OK this one isn't vital or anything, I was just curious...

Inevitably I run across a field that has spaces in the heading. E.G. Field 1. Recently I have done so with a huge table filled with these types of field names (i.e. field names with spaces).

When I drag and drop these fields down ina query, Access seems to recognize the field names with spaces and doesn't put brackets around these values by default in the GUI... however, if I click the SQL statement that is made, all the field names with spaces appear with brackets around them and the ones that don't have spaces do not.

The kicker is that if you now change this from a SELECT query to an APPEND query, when you choose the table to append to... Access will auto fill all the fields with the same names, put brackets around the fields that have spaces.

Then when you try to run the append query Access throws this error:
Quote: The INSERT INTO statement contains the following field name:'[Field 1]' Make sure you have typed the name correctly, and try the operation again. When you look at the SQL, though, everything that should have brackets does... so how do you fix it?

The answer is to remove the brackets in the GUI, but this is very time consuming if you have a table with a ton of "spaced field names".

The crazy thing is that once you remove the brackets in the GUI, Access remembers that you've deleted them. But where? How? Even if I remove the brackets in the GUI, if I then look in the SQL the brackets are there for both the INSERT INTO and the SELECT statement... So how does Access know, to leave the brackets off, at the append line, once you've deleted them?

Because I inherited a GIANT table filled with these types of field names, whenever I create an append table, I have to go through and delete all the brackets by hand... this is uber-lame!

Does anyone know of an easier way to deal with this? Or how I can get Access to force brackets initially, for field names with spaces. Seems that'd be a setting or something... why would they force brakctes for the instert but not for the select? It doesn't make any sense. Seems like if that throws an error they'd want to keep it consistent.

Anyway, if you have some insight as to the best way to deal with this, I'd be please to learn something new... I know what some of you might say... but please don't tell me not to use spaces in field names... that won't be helpful as I already know not to do this... like I said, sometimes the rest of the world doesn't know it's a no no

Thanks,
Gary

I've been trying to solve this problem all week, and will welcome any suggestions. I've searched the forums and MSDN library, and googled, but I must be missing *something*

I'm using Access 2000 and VBA

I would like to pass an integer variable (that corresponds to calendar months) to a function to return a string (with month name). I would like to use the string variable to refer to field names in a recordset.

I can call the FindMonthName function just as "FindMonthName(x)" when I'm not trying to return anything... but if I try to assign it to something I get a byRef argument type mis-match error.

I'm pasting in my code so far (I'm sure it's kind of spaghetti, this is my first time coding something that isn't a homework assignment! ):


	Code:
	Private Sub cmdBedDayBuckets_Click()

Dim dtmAdmitDate As Date
Dim dtmDischargeDate As Date
Dim intBedDayMonth As Integer
Dim intAdmitMonth As Integer
Dim intDischargeMonth As Integer
Dim strPatNum As String
Dim strRefNum As String
Dim strPlanType As String
Dim strAdmitClass As String
Dim arrLOS(1 To 12) As Integer
Dim strLOSmonth As String
Dim intMonthdiff As Integer
Dim intCounter As Integer

Dim varArray As Variant 'Variable array to hold recordset rows
Dim varLOSArray As Variant 'Variable array to hold LOS values

'declare DAO databases and recordsets
Dim BedDays As DAO.Database
Dim rstBedDays As DAO.Recordset
Dim rstLOSCY2004 As DAO.Recordset
Dim fldX As DAO.Field
strSQL = "tblTESTLOSBuckets"
strLOS = "tblLOSCY2004"
Set BedDays = CurrentDb()
'Open BedDays table recordset
Set rstBedDays = BedDays.OpenRecordset(strSQL, dbOpenSnapshot)
Set rstLOSCY2004 = BedDays.OpenRecordset(strLOS, dbOpenDynaset)
'Point to first record in recordset
rstBedDays.MoveFirst

Set varArray = rstBedDays.Clone
Set varLOSArray = rstLOSCY2004.Clone

'********************************************************************
'this section will need to loop while not EOF
Do While rstBedDays.EOF = False

    'assign field values to vb variables
    dtmAdmitDate = rstBedDays!AdmitDate
    dtmDischargeDate = rstBedDays!DischargeDate
    intAdmitMonth = rstBedDays!AdmitMonth
    intDischargeMonth = rstBedDays!DischargeMonth
    strPatNum = rstBedDays!PatNum
    strRefNum = rstBedDays!ReferralNum
    strPlanType = rstBedDays!PlanType
    strAdmitClass = rstBedDays!AdmitClass

    'compare Admission month and Discharge month to see if time
    'does not extend over multiple months
    If intAdmitMonth = intDischargeMonth Then
        'If admit month and dishcharge month are the same
        'LOS is equal to admit month
        x = intAdmitMonth
        arrLOS(x) = DateDiff("d", dtmAdmitDate, dtmDischargeDate)
        'try converting x (integer data type)to string data type
        strMonth = CStr(x)
       'refer to field name in rstLOSCY2004 with a variable that is directly related to x
        fldX = FindMonthName(strMonth)
        rstLOSCY2004!fldX.Value = arrLOS(x)
        'need to assign value in arrLOS to correct field in tblLOSCY2004
        'dont forget to insert strPatNum and strRefNum
    
        Else
            intMonthdiff = DateDiff("m", dtmAdmitDate, dtmDischargeDate)
            intCounter = 0
            Do Until intCounter = intMonthdiff
                x = intAdmitMonth + intCounter
                If x = intAdmitMonth Then
                    arrLOS(x) = DateDiff("d", dtmAdmitDate, FindLastDayInMonth(dtmAdmitDate, 0)) + 1
                    
                    Else
                    x = x - 12
                    arrLOS(x) = DateDiff("d", FindFirstDayInMonth(dtmAdmitDate, intCounter), FindLastDayInMonth(dtmAdmitDate,
intCounter)) + 1
                    End If
                    intCounter = intCounter + 1
                    Loop
                    x = intDischargeMonth
                    arrLOS(x) = DateDiff("d", FindFirstDayInMonth(dtmDischargeDate, 0), dtmDischargeDate)
                    
    End If
    rstBedDays.MoveNext
Loop


'**********************************************************************

End Sub
        
'Find First Day of Month Function
Function FindFirstDayInMonth(dtmdate As Date, intMonthcount As Integer) As Date
    'Return the first day in the specified month
    FindFirstDayInMonth = DateSerial(Year(dtmdate), Month(dtmdate) + intMonthcount, 1)
End Function

'Find Last Day of Month Function
Function FindLastDayInMonth(dtmdate As Date, intMonthcount As Integer) As Date
    'Return the last day in the specified month
    FindLastDayInMonth = DateSerial(Year(dtmdate), Month(dtmdate) + intMonthcount + 1, 0)
End Function

'Find Month Name Function
Function FindMonthName(strMonth As String) As String
    Select Case (strMonth)
        Case 1
        FindMonthName = Jan
        Case 2
        FindMonthName = Feb
        Case 3
        FindMonthName = Mar
        Case 4
        FindMonthName = Apr
        Case 5
        FindMonthName = May
        Case 6
        FindMonthName = Jun
        Case 7
        FindMonthName = Jul
        Case 8
        FindMonthName = Aug
        Case 9
        FindMonthName = Sep
        Case 10
        FindMonthName = Oct
        Case 11
        FindMonthName = Nov
        Case 12
        FindMonthName = Dec
    End Select
End Function

Thanks in advance for the help...

I am working on an form (using Access 2000 and VBA) that stores registration information, along with events that were attending, in a table and later retrieves that same information from the table to display the regist. info, events attended, and allows the user to change any current info along with adding new/recently attended events to each record.
I am currently allowing a max of six events, along with event details, to be stored and I am running through six different if statements to match the Event_List (name of the combo box that contains "Event 1 Event 2...Event 6") to the appropreiate information in the table. I have started to simplify the code to remove the redundancy by running a Do While to to find the correct Event by using an integer variable that starts at 1 and is incremented until it matches the 7th character (the number portion) of the Event chosen from the Event_List. After this I want to use that integer variable to say what event information in the table to gather. My problem is this: I have tried many different varieties of the following code and I am unable to make it work. I had never used Access up until a week and a half ago, and a few days ago I went and bought the book "Access VBA Programming for Dummies" but it doesn't help for this problem. Here's part of the code:

Variables (Event_Name, PMT, Receipt, Payment_Type, PIF) refer to Form textboxes while the variables in the [] refer to table field names.

'------Code I'm Currently Working On-------
Dim intX As Integer
Dim EventVal As Integer
Dim strX As String

intX = 1
EventVal = Val(Mid(Event_List, 7, 1))
Do While EventVal intX And intX < 6
intX = intX + 1
Loop

strX = str(intX)
Dim E_Name As String
Event_Name = [Event strX ]
PMT = [PMT strX]
Receipt = [Receipt strX]
Payment_Type = [Payment Type strX]
PIF = [Paid In Full strX]

'-----Old Code That I'm trying to simplify-------
If Event_List = "Event 1" Then
Event_Name = [Event 1]
PMT = [PMT 1]
Receipt = [Receipt 1]
Payment_Type = [Payment Type 1]
PIF = [Paid In Full 1]
Camp_Site = [Camp Site 1]
Cabin_Number = [Cabin Number 1]
ElseIf Event_List = "Event 2" Then
Event_Name = [Event 2]
PMT = [PMT 2]
Receipt = [Receipt 2]
Payment_Type = [Payment Type 2]
PIF = [Paid In Full 2]
Camp_Site = [Camp Site 2]
Cabin_Number = [Cabin Number 2]
ElseIf Event_List = "Event 3" Then
Event_Name = [Event 3]
PMT = [PMT 3]
Receipt = [Receipt 3]
Payment_Type = [Payment Type 3]
PIF = [Paid In Full 3]
Camp_Site = [Camp Site 3]
Cabin_Number = [Cabin Number 3]

PLEASE HELP!!!!

Help!

I have a query with enumerated field names (i.e. CBL_1_kW,CBL_2_kW,CBL_3_kW,......CBL_10_kW)

I'm trying to construct the field names with a counter and feed them into an array where I can sort them later. (i.e. rs!["CBL_"& Str(i) & "_kW")

The problem I am having is that the program fails to compile when the field name is constructed in the for loop with the counter (i). If I manually type in the variable it works find but this is tedious and poor programming I would think.

Any advice?


----------------------

type MeterReading
Date as variant
kW as variant
end type

dim MeterReadings(1 to 10) of MeterReading


rs.movefirst
do while not (rs.eof)
for (i=1 to 10)
with MeterReadings(i)
.Date = rs!["CBL_"&Str(i)&"_Date"]

How can i refer to a field name of a RecordSet variable if the field in question has a space or other funky character?

	Code:
	
REM the name of the field in this example is Field #Name

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Table 1")
...
SubRoutine(rst!Field #Name)
SubRoutine(rst!Field #Name)
SubRoutine(rst!"Field Name")

And I can't think of anything else to try

Hi Everyone:

I have a project of migrating our Access data to Oracle. One of the pre-reqs the boss wants is to uppercase all of the column names (Field name) for all of the tables. I want to avoid manually going in all the tables and changing the case of the columns because there is like 300 tables where dealing with. I was wondering if in Access..is there an efficient way of converting my column names to upper case via script? VBA or Macro?

Can I pick someones brains....!!!

I want to access and loop through field names using vba code

for eg. - fld1, fld2,fld3...etc

for i =1 to 3
if fld(i).value=0 then
fld(1).visible=false
next i

is this format possible in access vba??

Is there a way to reference a field name from a variable?
for instance:

i have a field on a form named q1 I would like to reference this field from the qty expression that i set and then use the qty in the Me.qty.enabled expression. Is there a way to do this?

Dim cont as string
Dim qty as string
cont = 1
qty = "q" & cont
Me.qty.Enabled = True

OR...

can i just use Me.[q & myNum].Enabled = True

I need to loop through a bunch of controls that all start with q and have a different number after it.
Any ideas?

thanks
-kermit

Dear All,

Does anybody know how to rename field names with a help of a macro or with VBA?

How I do that at the moment is that I created another table with the correct field names and I append the data with a query
It is quite a good solution but there is huge data movement (approx 30 000 lines), so it would be better somehow to rename the fields only

Many thanks in advance

I am trying to change the field names of a table that is created from a text document that I import into Access. The field names are set to F1, F2, F3, etc. I was wondering if there is some way to have a macro slap specific names on those fields.

How do you set a form control based on a value in an ADO recordset?

For example, say you have a recordset "rst" which has a field called "Name", which in turn stores a numerical value based on which name it is supposed to represent. So the rst field "Name" will hold things like: 1, 4, 7, 3, ...etc. Which then map to a name in another recordset.

So how would you take that number stored in the "Name" field and make a control on the form match? The control is a combobox which has the first (hidden) column set to the same numbers, and displays the name I want.

In otherwords, when the combobox lists "Brian", the hidden first column reads "3". Now I want to call the recordset field "Name" get the "3" and set the control to read "Brian".

Is that possible?

Is it possible to rename table field names with code. I have a spreadsheet that is beeing imported from Excel and I would like to set the headers once the table is imported into Access.

I know that I can have Access assign the first row as field names but that approach will not work in this case. Any ideas?

Does anyone know if you can use a string or variant variable in place of a filed name. I have multiple field names with the format month/year-char, for example 10/06-PE. I would like to be able to use a variable in place of the field name so that I do not have to code multiple case or if-else statements for each possibility. Here is what the code that I have looks like. The "fld" that I have is set as a string to the field name that I want. but when I run, the compiler is looking for a field named fld and not the value of the string. Any help would be appreicated. Can this be done? Thanks


	Code:
	Set db = OpenDatabase("P:SafetyData.mdb")
         Set rs = db.OpenRecordset("AllDates")
         Dim fld As String
         
         dt = Date
         mon = Left(dt, 2)
         yr = Right(dt, 2)
         fld = mon + "/" + yr + "-" + act
         
         With rs         
             .Index = "PrimaryKey"                    
             .Seek "=", Me.EmpList.Value             
             .Edit                              
             ![fld] = Date             
             .Update             
         End With
         
         rs.Close
         db.Close



i have a field name with a space in it. With the code im using how can i stop it brining up the error.

expected: list seperator or )

the fieldname is CFirst Name

I am thinking of moving into SQL Server 2008 in the future so I am wondering about Field Names. Is it ok to have spaces in them ie Date Created or is it better to use DateCreated?

I've read somewhere SQL servers do not like spaces in names but I am wondering if this would apply if I plan to upgrade to MS SQL Server 2008 R2 from MS Access 2010.

Thank you.

When copying information from Access and pasting into Excel does anyone know how to NOT paste the Field names with the information? It's very annoying to have to paste the information next to my excel list and then have to copy and paste the cells into position without the field names.

Thanks in advance.

I have just about cleaned up my data case, lots of field removals, streamlining of macros and code, removal of redundant tables, queries etc. But I have one last part to go and that concerns field names with two words like First Name, Last Name etc. (as a side note these names are there because I originally used an insurance company quote/policy managemnt system as my starting point back in 1995) I figure the bloke in the insurance company who made their system was too young to have been on DOS where LastName etc was standard.

Fortunately I don't have too may of them as most of the fields I did myself

When I deleted fields from the the tables I just went to the queries and deleted all the fields that were like Expr63 etc.

But for changing the field names I just tried "Track name AutoCorrect info" but the were results were very inconsistent. In some queries all the new names were there but with others there were no changes. I am wondering if I did I wrong or it is not a good tool and I am better off it to do it manually.

On a side note I assume I could code with Find and Replace. Macros...well that is a manual exercise

I need to export an excel spreadsheet file from a query or table made by a query and the field names need to be PO.Item or say PO.Company. Access will not let me use a . in a field name is there a work-around?

Thanks
Steve

I"m using a field name in vba the problem is that the name of the field is % Complete, I get a syntax error when I run the code, I have included brackects between them but then the code doesn't recoginze the field name. Help please,

I am gathering information from a user form, using it in calculations and writing it to a table. The fields I am writing to are named F1, F2, F3... F24. Instead of writing add record code 24 times (many times over) I would like to use a variable in the field name and iterate the variable. I have tested the following code but receive "object not found in this collection" error messages:

dummy = 1
For i = 1 to 24
Table1!["F"+str(dummy)] = calculation
dummy = dummy + 1
Next i

Because of the "STR" function I've also tried this code using field names with a space between the F and the number (ie F 1). I've heard this is possible but my numerous variations on the above test have all failed. I'm working in Access 97 and don't have much experience with advanced VB programming. Any solutions would save me writing hundreds of lines of code. Thanks

Hello All,

I am importing data from an Excel file into Access 2010 that comes from a survey. The first row of the excel File contains the survey questions that have been broken down to give a brief summary of the actual question. I looks like Access only likes Field Names with length less than or equal to 65 characters.

Is there a way to increase this?

Thanks!

Hi All,

I am really struggling with exporting a query into excel without saving. I am currently using the below code which exports the query into Excel without issue, the only issue I have is there is no field name/column heading exported across.

The below code does not include my many attempts at this, but gives you an idea of what I am trying to do:

Dim dbs As DAO.Database
Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object


Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook


objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "Photo Austria"

Set rstGetRecordSet = dbs.OpenRecordset("y_AT_02")

objActiveWkb.Worksheets("Photo Austria").Cells(2, 1).CopyFromRecordset rstGetRecordSet
objActiveWkb.Worksheets("Photo Austria").Columns("A:Z").EntireColumn.AutoFit


Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing


Not finding an answer? Try a Google search.