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]
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.
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
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
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.
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!
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?
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 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 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
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 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?
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:
intMyFldName strMyFldName etc...
Where as in the past I would do something like:
my_fld_name_one my_fld_name_two etc...
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?
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?
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.