Using Variables In A Form To Reference Table Field Names

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

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]


View Replies


Table Field Names On Form - Version: 2000 (9.0)

Could someone please help me with a code (VB) to show all the field names of a table on a form? I've searched but did not find any existing codes.

Thank you very much in advance.

View Replies View Related

Field Names Don't Match Names On Form

I have a form with several data fields on it. I also have a button on the form that allows the user to duplicate a record . The reason for this duplication is so that if there will be an additional client record for the same customer, but only one piece of data will need to be changed, it's easier to copy the record and then change the one field.

However, I am getting the following message:

"some of the field names you tried to paste don't match fieldnames on the form"

and then not all data in all fields gets duplicated.

I need to figure this out, but am going nuts with it. If anyone has an idea or two they'd care to toss my way, I would be happy.

Thanks one more time, in advance!!

View Replies View Related

Variables In Report Names - Version: 2007 (12.0)

I am using Access 2007

Is it possible to substitute a variable in a report name declaration?

That is, can I substitute this:

Reports![strName].lblDesc.Visible = True
For this:

Reports![rpt003].lblDesc.Visible = True

Where strName = rpt003

View Replies View Related

Access Field And Table Names


Recently I have started working for one of the company where I have to deal with one of the access file. this file has lots of tables containing many fields.

My question is

How can I get all the tables name, their fields and attributes in Microsoft Word file. I have tried opening table > design view and copy text but it doesn't work. also tries coping table and paste in in word file but it takes ages

any suggestion will be helpful

Thank you

View Replies View Related

Reference A Field On A Sub Form

Hi All

I am trying to reference a field on a sub form from a button on the main form. I want to read a field called "Date1" and I want Date1 from the first row, regardless of which row has been selected by the user. The statment used is


Any ideas PLEASE


View Replies View Related

Print A List Of Table Field Names

Just a quickie- i hope. :rolleyes:

I am in the process of trying to merge two large databases. I have four tables that really should be one to work in the new DB. I just need to be able to print a list of fields in each table so that I can work on the new combined structure before making the changes. I know I can use the documenter but this produces a very comprehensive list which is much more than I need.
Is there anyway of producing a similar list without the detail, just showing the field names.


View Replies View Related

Update Field Based On A Reference Table

I would like to update two fields [Category] and [ProdType] in tblAccum based on a reference table.

The reference table is tblReference and contains the fields [Code], [Category] and [ProdType].

tblReference example of field values:

Code Category ProdType
A Blank Accessory
BS Blank Blank Stock
O Printed Offset

So if the Code field in tblAccum has a value of O then based on the tblReference table the Category value would be Printed and the ProdType value would be Offset.

Any help is greatly appreciated.


View Replies View Related

Use Form With Duplicate Field Names

Hi Again everyone,

I have a form say with 20 text boxes. I would like to have these text boxes indexed for earier access etc. So I would have them listed like so....

TxtBox(2) and so on......

The problem I have is that everytime I enter the name TxtBox within the form textbox control name more than once. I get the following error.

"You entered the control name "TxtBox" which is already in use."

Why can I not make Access setup an array for the text boxes? How do I do this?

Thanks for your help!

View Replies View Related

Is There A Way To Alphabetize Table Field Names When You Look At Them In Design Mode?

Other than manually moving fields in a table in design mode, can I get the fields alphabetized?


View Replies View Related

Is There A Way To Alphabetize Table Field Names When You Look At Them In Design Mode?

Other than manually moving fields in a table in design mode, can I get the fields alphabetized?


View Replies View Related

Return Table Field Names - Version: 2003 (11.0)

Hi everyone. I have some code that will print out the field names of my table:

Sub getsomefieldnames()

Dim db As Database
Dim fld As Field

Set db = CurrentDb

For Each fld In db.TableDefs!tblAppeal.Fields
Debug.Print fld.Name
Next fld

End Sub

How would I modify this to have it print out the fields seperated by a comma on one line? Sort of like this:

Appeal_ID, Team_Lead, Analyst

Any ideas?



View Replies View Related

Reference A Field On Parent Form From The Child Form


how do i reference a field on the parent form from the child form e.g.

i have a button on the child form which needs to read a value on the parent form and use it i na calculation etc


View Replies View Related

Populating A Field Via A Reference Table - Version: 2000 (9.0)

So here's the deal... i'm stumped on this one. I have a DB that is for tracking baseball scores. There are two tables:

MainTable -> this one is where the users will input data about the games. In each record, these are the relevant fields to my question: VCity, VName, VAbbrev. VCity is the team's city (ie: Milwaukee). This will be selected by a drop-down box where all 30 teams are listed.

MLBTeams -> this one is a static table, 30 entries of every MLB team. TeamCity (which also populates the VCity pulldown in MainTable), TeamName (ie: Brewers, Braves, etc) and TeamAbbrev (ie: ATL, NYM, NYY, etc).

What I can't figure out how to do is automatically populate MainTable.VName and MainTable.VAbbrev based upon what's selected in VCity (which is the only selectable part of the record on the form). So - in short - I need way a way to do this:

If MainTable.VCity = "Cincinnati" then
MainTable.VName = "Reds" and
MainTable.VAbbrev = "CIN".

All that data can be pulled from the MLBTeams table. I think I need to set it as a control source on VName & VAbbrev, but i'm not sure how to do it. any help would be greatly appreciated!

View Replies View Related

Reference Table Field With A Space In It - Version: 2003 (11.0)

Hello UA! I am adding data to a table via

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("Contacts")

and this line works...

rst!State = something

but this line does not...

rst!Zip Code = something

and neither does this one...

rst!Zip_Code = something

I took the spaces out in the code and in the table, and it works fine, so it is definately the space between "Zip" and "Code". I used an underscore in place of the space, but no luck. Is there a way to do this with a space?

View Replies View Related

Insert Field Names And Values Into Table - Version: 2003 (11.0)

I am trying to convert record from a table that stores information horizontial into a vertical table.

Here is how the information is currently stored:

CT_ID, Run_Date, ASA, AHT, Staff

CT_ID, Run_Date, Field_name, Field_value

Any help is greatly appreciated

View Replies View Related

DAO: Resolve Field Names Read From Table - Version: 2003 (11.0)

would like to substitute values stored in a table for placeholders in a string. The table has two fields: "Text Value" (,atches the placeholder) and "Access Field Name" (reads from table a field name of an active recordset).

Consider this code:

With rs3


Do While Not .BOF And Not .EOF

fldFrom = ![EMail_Text_Value]

fldTo = ![EMail_Access_Field_Name]

strMsg = Replace(strMsg, fldFrom, fldTo)

Debug.Print strMsg



End With

The above code cyles through the fields and makes the substituions. Rather than getting the values I hope for, I get instead the names of the feilds themselves. For instance, when



and the message is

Dear <Fname>,

Thank you for your...

after substitution, "<Fname>" becomes

Dear !Name_First,

Thank you for your...

rather than the value of !Name_First, which, as I said, is a field in a recordset. How do I force it to resolve?


I closed this thread because it is a double post.


View Replies View Related

Get Table Field Names And Order Them In A Combobox - Version: 2000 (9.0)

I would like to get the names of the fields in a table and then order them in a listbox. I know how to use the TableDefs, but can't figure out how to order the field names when they are returned by the TableDefs. Can I do this without using a temporary table to help with the ordering. I'm using Access 2000, so AddItem doesn't work.

Thank you.

View Replies View Related

Importing Excel Sheet Into Table: Do Field Names HAVE To Match? - Version: 2003 (11.0)

I have an Excel sheet that I want to import into a table.

Now when I try to import it into an existing table it rejects and the reason for this appears to be because the field names are not matched.

For example my table is: "Customer ID" while the Excel sheet is "Customer_ID".

I was thinking that the data would be imported in the order of appearance, so the first column in Excel goes to the first column in my table.

Is it possible to setup a mapping configuration so Excel Column X goes to Table colum Y?

View Replies View Related

Adding Multiple Names To Single Field On Form - Version: 2003 (11.0)

I have a subform ECNDetailfrm with a field?? named
Engineering Distribution. When this field is clicked
another form is opened up with the names of the Engineers
that can go in this Engineering Distribution field.
Multiple names can go into this field. This field is for
viewing to show what engineers received the ECN each form
is based on. The code below is the code I have that inserts
each name that is selected to the field. My problem is:
In the table ECNDetailtbl where the Engineering
Distribution field is located only one name will show
up in the table. How can I get multiple names to show.

Code: Dim varItem As Variant
Dim strEngineer As String
For Each varItem In Me.MfgEngList.ItemsSelected
strEngineer = strEngineer & Me.MfgEngList.ItemData(varItem) & vbCrLf
Next varItem
strEngineer = Trim(strEngineer)
Forms!ECNBCNVIPfrm!ECNDetailfrm![Engineering Distribution] = strEngineer

View Replies View Related

Variables In Text Box Names (VBA) - Version: Any Version

This is probably a silly question, but...

I have a report that is based upon a crosstab query. The report is showing planned hours to be worked over a set of weeks by a staff. Right now I manually change the dates of the weeks each time that I update the database for a new season. (Nice for me and my billing, but a PITA) Usually the crosstab results are in a subreport.

What I would like to do is have a set of unbound text boxes for the week labels and fill them in when the report opens.

I can do that now with a line like this:

Code:WeekName = DLookup("[WeekLabel]", "tblWeeks", "[WeekNumber]=" & 1)
Me.Week1Name = WeekName

And I would need to do 16 or 20 lines for the weeks of the report (some reports have multiple week labels, so I would need to do this twice)

To clean this up I would like to do a For...Next loop and have the loop go through the number of weeks and have a line of code for each label.

My problem is I can't get the "Me.Week1Name" to accept a variable. I have tried:

Code:strWeekNumber = "Week" & x & "Date"

And I get an error that my VB Help doesn't explain, it just hangs.

So can I do this, and if so how?



View Replies View Related

Form Event (Before And After Update) Copy Form/Field Value To Another Table/Field


I thank you in advance for considering this inquiry.

From within a Form.field (based on a master table query), I desire to trigger two events; one before update and one after update.

This function will serve to document specific form.field value changes to a "log" table for review prior to being committed back to the master table.

What I'm looking for are functions, which I can use in a Macro or VBA code to facilitate a field read and copy before change and a read and copy after change triggering.

I'm sure this is absurdly simple.

View Replies View Related

Field Names

If 2 tables have a field named 'EmployeeID' (for example), are you screwed when it comes to queries and vba, as far as selecting fields / specifying data goes?

Thank you!

View Replies View Related

Field Names

I need an opinion. I'm new to access so I'm really lost I have to create a database consisting of 20 clients. Then they gave me 4 steps I had to accomplish:

Create a total of all account balances, so the total number of recievables are known.

For each account, calculate the number of days each balance has been outstanding.

Classify the account into 4 groups three late (30,60,90 days overdue) and one current (under 30 days), Total the amount of outstanding recievables for each catagory.

Sort using number of days balance is outstanding as primary sort key and outstanding balance as the second sort key.

My question is should I make a field called Days overdue and a separate one for outstanding balance. It also seems they want me to do calculations in the table. I thought that was not an excepted taboo? Are the 4 goals possible to accomplish in access? Please give me some help!

View Replies View Related

Send A Content Of Unbound Field In A Form To A Field In The Table

in my form I created an unbound field with a requested combination, now how can I put those in a table field.
in other words, how can I send the content of a form's unbound field to a field in the table?

your help is very appreciated.


View Replies View Related

Missing Field Names

Just used the code below to output to .csv. however Field Names have been omitted from the output .csv file.

Can anyone help?



Private Sub export_Click()
On Error GoTo Err_export_Click
Dim AString As String
AString = "Export_Occupancy_"
DoCmd.TransferText acExportDelim, "", "ChildCare Vouchers For Accor", "c:\Temp" & AString & Format(date, "YYYY_MMDD") & Format(Time, "-HH_MM") & ".csv"
Exit Sub
MsgBox Err.Description
Resume Exit_export_Click
End Sub

View Replies View Related

Export Field Names

I need to capture the field names from a linked table and copy them into an excel spreadsheet. Does anyone have an idea how I can accomplish this?


View Replies View Related

Export Field Names Etc.


I have succeeded (with some help from this site!) in creating a clinical database for the Diabetes Care team in our hospital. Now, our IT helpdesk staff want me to list the field names and data types. The design view of the tables seems to contain everything they need, but I have tried everything and cannot find a way to paste this info. out into any other format, Word, Excel etc. Has anyone ever managed this or is there something else I should do?

View Replies View Related

Prefix's For Field Names

I have been looking at the way I name fields and thought I'd Google the topic. Seems to be a split as to prefix names or not. Looks like the SQL Sever and Oracle groups say not to while Access users are kind of directed to use them...

While I have not prefixed names in the past, I was going to do my next db with the things like:


Where as in the past I would do something like:


What do you all see as some of the pro's and con's?

View Replies View Related

Field Names In List Box

I would like to have a listbox that contains all of the fields names from a table. Since the user will be selecting items from this list box, the field names should be referred to by their captions as defined in the table.

The listbox values will change depending on the value selected from another listbox.

Question: using code, how can I fill a list box with field names and display the field caption?


View Replies View Related

Access Field Names

I'm getting the following error :

Microsoft OLE DB Provider for ODBC Driverserror '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Description One LIKE '%flip%''.
/sbs/search_prods.asp, line 22

I know that it's becuase the field name is Access is "Description One" as opposed to "DescriptionOne" or "Description_One".

The problem is that I can't alter the actual DB table (it is part of another system that I'm attempting to integrate the site with). Is there anyway to write a query statement that will allow for field names to have spaces in them?


View Replies View Related

Changing Field Names

I am using ASP to try to edit the column (field) names of my Access database. Here is an example of would I would like to work:

Code:data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ Server.MapPath("databases/logins.mdb")Set rs = Server.CreateObject("ADODB.Recordset")rs.CursorType = 2rs.LockType = 3 rs.Open "SELECT * FROM Users;" , data_sourcers(1).name="blah"

The last line is the problem. A field name in an ADO recordset is limited to read-only persmissions for opened (already exisiting) recordsets.

Is there another way around this without using an SQL "ALTER" statement. In other words, by accessing the field's name through a number like rs(i).name instead of rs("fieldname").name?


View Replies View Related

Rename Field Names Using VBA

Hello, I'm currently working on automating the import of a csv file (which works fine using the Transfertext method) but the csv file does not contain field names.

I want to rename the fields with something meaningful after importing the file, but I can't seem to figure out how to do this using the tabeldef method of handling tables. There's nothing in the help, not that I could find anyway.

Does anyone know whether this can be done and how, cheers.

View Replies View Related

Copyrights 2005-15, All rights reserved