Using Variables In A Form To Reference Table Field Names

May 24, 2006

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


Modules & VBA :: Variables As Field Names In Recordset

Mar 12, 2014

I am using Access 2013 under Windows 7. In my database I have a table, tblStock, with field names Module, Component_1, Component_2, etc. up to Component_50. I also have fields Qty_1, Qty_2, etc. up to Qty_50. These field names are not easily changed as they are constantly updated from another database. I want to create a table, tblTempBOM, using VBA by selecting a particular value of Module selected from a combo box on a form, from tblStock and creating a record with the Fields "Module", "Component" and "Qty" for each Component and Qty from 1 to 50. I am trying to use a From... Next... loop to cycle through the Component and Qty fields and store the data in the new table.

Dim strModule As String
Dim strSQL As String
Dim strQty As String
Dim strEye As String
Dim strTest As String


This runs to the point where I try to set the value of rcd![StockCode], but the item rcdStock![strTest] has no value.Can I reference the value of the field in the rcdStock Recordset in some other way that would work?

View 5 Replies View Related

Forms :: Display Field From Separate Table In A Form To Use As Reference

Mar 25, 2014

In my form (source tblJobs) i input a contract number from tblContracts. but i would also like it to show the contract address when the contract number is entered. Both fields belong to tblContracts but i only wish to enter the contract number, the address will just be used as a reference as it isnt something i would have to enter again.

So for example

112 - Main Road

If i enter 122 in my ContractNo field i would like it to show the contract address for that specific contract.

Can i do this? If So how?

View 5 Replies View Related

Forms :: Form Wizard Not Displaying Table Field Names

Apr 25, 2013

Access 2007 Pro.Attempting to use a query and a table to populate a 1:N form/subform using the Form Wizard. The wizard will not display the field names of the query.

View 2 Replies View Related

Field Names Don't Match Names On Form

Nov 7, 2006

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 1 Replies View Related

Text Box To Display Names Using Foreign Key As Reference

Aug 27, 2015

I want a text box which is on a form linked to a table, to display a name using a foreign key as a reference.

I just had it semi working with D lookup and the whole database corrupted.

E.g. site_ID would be changed to Site_name on JobsF using JobsT as the record source

the site_ID is dependent on Job_ID

View 8 Replies View Related

Tables :: Look Up Reference For Table Field

Dec 5, 2013

When entering a lookup reference to a table field it pop up message saying, "you can't modify the table 1 this time as it is in use by other person or process" i have closed all forms and table reports but the problem is same to rectify ? also when going to see the realationship then there shows no relation ship developed even after the lookup reference from one table filed to another table field.

View 1 Replies View Related

Tables :: Reference A Table Field

Apr 10, 2013

I have two tables, Table 1 and Table 2 and two related forms, Frm1 and Frm2. I want to be able to update the data on Table1 from Frm2, that is, if I change the data on Frm2, the corresponding fields in Table1 will update to the same data. I know that if both tables are open I can reference the fields on Frm1 and Frm2 with an IF statement: If Active = True And Forms!Frm1a.DoNotchange = False Then..And I know on the update query I can reference a control on a form by Forms!Formname.Control. Wll works fine.What I would like to know is can I reference a table field in the same way. For example, I have tried to use the IF Statement If Active = True And Tables!Table1.DoNotchange = False Then..Is there a way to reference the Table instead of the Form in both the VBA and the query? The problem seems to be that I have the two bound controls [Active] on Frm2 and [DoNotChange] on Frm1. (Which I need).

View 5 Replies View Related

Update Field Based On A Reference Table

Mar 15, 2006

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 7 Replies View Related

Cannot Reference A Table With Multivalued Field Using IN Clause

Jul 3, 2014

I have an access db that is linked to a sharepoint list (the list originated in Access).I tried a simple select query with parameters to search for by id and another query of the same table to search by date.They both come back with the same error of "cannot reference a table with a multivalued field using an IN clause that refers to another database"Iv'e gone through the table and these fields that the query is using and none seem to have any multivalues.

View 6 Replies View Related

Modules & VBA :: Test If Form Field Fits Within Range Variables

Mar 4, 2014

I have an Access form that gathers students test scores on Reading & Writing exams. Depending on those scores, there are five possible classes for them to be assigned.

I have the intervals worked out, and have created a table that contains the min reading, max reading, min writing, max writing, and class they belong in.

As follows:


readMin readMax writeMin writeMax englishPlacement
0 99 0 5 Eng1
55 69 6 6 Eng2
70 99 6 6 Eng3
0 69 7 99 Eng4
70 99 7 99 Eng5

The scores are entered into a form as scoreReading & scoreWriting (integer)

I'm trying to figure out the best way to pull and compare the values and return the proper englishPlacement text. Its set up this way because there are multiple tables to pull from, depending on the date of the exam. Each table has different ranges for each Eng1,...,Eng5.

I'm thinking perhaps to iterate through each column and evaluate T/F, then return the englishPlacement value of the correct row. I'd like to keep this stuff in the VBA so that the code to select a table based upon date doesn't break when the intervals inevitably change requiring an additional table.

View 2 Replies View Related

Reference A Field On A Sub Form

Mar 25, 2006

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 2 Replies View Related

Tables :: Possible To Have The Field Names In Table Be Set By Values In Another Table?

Jul 27, 2014

Is it possible to have the field names in a table be set by the values in another table? The desire being that for a database used in various locations, the local variations could be changed in one table which would then propagate that change throughout all the forms, reports, tables etc.

View 14 Replies View Related

Modules & VBA :: Reference Field On Form

Apr 21, 2015

I had this line :

GBL_Tab05ID = Me.Tab05ID

and wanted to change this. I have this on the On Current event and it gives me an error because the data is not in the record source when the form opens. How can I change the me. to the formname? The form gets dynamically created so i have

Dim CurrentForm As String
CurrentForm = Me.Form.Name

then i put

GBL_Tab05ID = Forms(CurrentForm).Form.Tab05ID

but that did not work...what is the correct syntax?

View 5 Replies View Related

Access Field And Table Names

Jun 30, 2005


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 2 Replies View Related

Print A List Of Table Field Names

Dec 20, 2005

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 14 Replies View Related

General :: Append Table Vba Need Field Names

Nov 27, 2012

I should change the Fields(1) and Fields(2) to the actual fields names.

field 1 should be EmployeeListID
field 2 should be ItemsID

PHP Code:

Dim rs As dao.RecordsetDim db As dao.DatabaseSet db = CurrentDbSet rs = db.OpenRecordset("tblEmployeeItems", 
dbOpenDynaset)rs.AddNewrs.Fields(1) = Me.[lstAvailableEmployees]rs.Fields(2) = Me.
[lstAvailableItems]rs.Updaters.Closedb.CloseSet rs = NothingSet db = Nothing 

View 3 Replies View Related

Tables :: Delete All Field Names From Table

May 21, 2015

I need to delete all the field names from my table so I can import a new excel file with different field name headings,

currently I run:

DoCmd.RunSQL "DELETE * from table1"

this deletes the data in the table, but not field names.

What Vba command will allow me to do this.

View 9 Replies View Related

Can Relate Field Names To Primary Key In Another Table?

Jul 17, 2012

I am a new access user and I have several questions primarily can i relate field names to a primary key in another table?

View 9 Replies View Related

Reference A Field On Parent Form From The Child Form

Dec 4, 2005


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 1 Replies View Related

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

Jul 20, 2006

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


View 2 Replies View Related

Tables :: Importing Excel Table With Invalid Field Names

Jun 3, 2015

I would like to import excel sheet that contains about 45 fields and the fields names are not well defined(not obey the Access rules). I would like to import the sheet into ms access without changing the excel sheet because i have to provide only the interface to users where they have to import sheets only. (All the time In Excel fields name are arranged and in specific order but invalid names of fields).

After 1 week the person has new excel sheet with same format and the process of importing data into ms access will be continue for whole year or more.

View 4 Replies View Related

Forms :: Cascading Combo Boxes For Table And Field Names

May 15, 2013

Currently, I have a form with a combobox that lists all of the names of the tables inside my database. Depending on the table selected in the first combobox, I would like to have another combobox which allows the user to choose from the field names inside that table.

View 3 Replies View Related

Forms :: Add Multiple Checkbox Names Into Single Table Field

Aug 15, 2013

Im constructing a form with check boxes next to names. Id like to have the checked names input serially into a single table field like:

Name1, Name2, Name3

There are 8 possible Names. Is there way to do this without a long complicated If-Else-Then function?

View 5 Replies View Related

Queries :: Yes / No Field As Bookmark In Table With Names - Open To Last Or First Check

May 21, 2013

I have a yes/no field as a book mark in a table with names. How can I query it to open to the last or the first check?

View 13 Replies View Related

Use Form With Duplicate Field Names

Jan 2, 2005

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 3 Replies View Related

Copyrights 2005-15, All rights reserved