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]
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.
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!!
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
Reports![rpt003].lblDesc.Visible = True
Where strName = rpt003
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
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
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.
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.
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!
Other than manually moving fields in a table in design mode, can I get the fields alphabetized?
Other than manually moving fields in a table in design mode, can I get the fields alphabetized?
Hi everyone. I have some code that will print out the field names of my table:
Dim db As Database
Dim fld As Field
Set db = CurrentDb
For Each fld In db.TableDefs!tblAppeal.Fields
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
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
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!
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?
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
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:
Do While Not .BOF And Not .EOF
fldFrom = ![EMail_Text_Value]
fldTo = ![EMail_Access_Field_Name]
strMsg = Replace(strMsg, fldFrom, fldTo)
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
Thank you for your...
after substitution, "<Fname>" becomes
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.
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.
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?
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
strEngineer = Trim(strEngineer)
Forms!ECNBCNVIPfrm!ECNDetailfrm![Engineering Distribution] = strEngineer
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?
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.
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?
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!
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.
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"
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?
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?
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?
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?
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?
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?
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.