I have a 3 column list box in the format:
A1 A2 A3
B1 B2 B3
C1 C2 C3
The rowsource for this contains A1;A2;A3;B1;B2;B3;C1;C2;C3
I want to reverse the order so I have
C1 C2 C3
B1 B2 B3
A1 A2 A3
Which would give a row source of C1;C2;C3;B1;B2;B3;A1;A2;A3
Using a popup form
1. On my main form, I have a listbox, I would like to edit the values of the listbox.
To do this, I have a popup form with 2 listboxes, one to have the values of the listbox on the main form, and the other listbox with option values for the 1st
1) how to i pass the rowsource sql of the listbox on the main form to the listbox on the popup form
2) how on closing the popup form, do i update the rowsource sql listbox on the main form from the changed value of the popup form listbox rowsource sql
I am trying to retrieve a record from a table (M_Paint) using a text box (txb_oldrecord) and a command button (cmd_getrecord). I want the record to show up on a list box (lst_display). I have written the following code:
Code:Private Sub cmd_getrecord_Click() With Me.lst_display .RowSource = _ "SELECT [M_Paint].[Old_Code], [M_Paint].[Supplier], [M_Paint].[Old_Color], [M_Paint].[Metallic], " & _ "[M_Paint].[Color_Number], [M_Paint].[Finish_Comments], [M_Paint].[Size], [M_Paint].[Number_of_Samples], " & _ "[M_Paint].[Project_Number], [M_Paint].[Date_Received] " & _ "FROM M_Paint " & _ "WHERE [M_Paint].[Old_Code] = '" & Me.txb_old_rec.Value & "';" End With End Sub
When I test this, I get the following error:
Quote: Data type mismatch in criteria expression.
What am I doing wrong?
Thanks in advance!
I have a form with many comboboxes. The comboboxes need to have their original rowsource set to a table in my database. However, when the user selects a value in a combobox, it is programatically fed into a SQL string which serves to populate a subform. This part all works great.
However, I'm trying to reset the comboboxes as well so that options will "narrow down" until only a single option is possible. So far, I have:
Code:
Private Sub cmb_Author_Change()
Dim strSQL As String
strSQL = "SELECT tbl_Records.RecordName, tbl_Records.RecordDistinction, tbl_Records.Title, tbl_Records.Author, " & _
"tbl_Records.ProjectManager, tbl_Records.[Site Name], tbl_Records.ChargeCode,
[Code] ....
GetWhere() is the portion that feeds info from the comboboxes to the Finder string.
The red highlighted bit is the part I'm working on. Obviously, once I figure out the proper syntax, all of the other
comboboxes will be set up the same way.
What's funny is that in testing (all of the data currently input is simple and allows me to select options where I know what the response will be in other boxes), when I change Author and go to use cmb_RecordName, it replies that "the record source 'My Data' specified on this form or report does not exist" (where 'My Data' represents real data).
So, clearly, it is finding the data, just not using it as a record source, but rather believing that the name of the recordsource is the value.
I would like to update the SQL for a subform. I have an SQL statement that I have placed into a string and now I would like to update the subform using it's RowSource property, but I get an error when trying to use the following statement:
Forms![frmModelSales].[frmModelSales-Subform-
QuestionableShip].RowSource = strSqlForSummary
(the message board has split this into two lines, it is really one)
I have a form that contains a combo box, I used the access wizard to make this combo box select a record from a table of customers and display that record on the form.
The user then has the option to print that record.
This works fine.
I now want to be able use the same combo box but with a different table so I can select Records from a list of suppliers.
Is there a way I could use a toggle button to change the rowsource of the combo box and then use that same combo box to select records from my supplier table.
I have a combo box which I define a SQL statement as its rowsource.
When I open the form, I have yet to define the SQL statement for the combo box.
My code is:
if me.cmbobox.listcount = 0 then
me.cmbobox.enabled = false
else
me.cmbobox.enabled = true
end if
But it shows the box as still enabled.
When I do debug.print me.cmbobox.listcount, it shows a value of 1. But that is impossible since I have yet to define a rowsource for it or its empty.
I also have column heading turned off too.
I'm developing a form to link a clause to one of a selection of internal documents from drop down boxes (of which there are a lot and hence looking for ways to make it more manageable to find).Now the setup of the internal documents is unfortunately beyond my control (separate SharePoint Libraries, inconsistent fields, you name it they've picked the worst way to do it :P).
I figure the best way to do this is to set up some unbound controls - two drop down (to narrow down location/department), two text boxes (doc code and name) with the idea that it will limit the selection to anything 'like' what is typed in.What I would like is either: How to set the RowSource criteria to only apply if the relevant control is not blank (i.e. if all the boxes are blank, all docs will show; if 'Sales' is selected in department and the user types 'Sales Order Entry' only the Sales Documents relating to order entry will appear)How to add criteria to the RowSource of a dropdown box via VBA (I've created the relevant 'If then ... else' statements but not sure how to add rowsource criteria in VBA)
I created a combobox with 2 columns, and i donīt know how to fill it using code, I tried with :
combobox.AddItem ("table.fields(0) ; table.fields(1)")
also tried
combobox.rowsource
but when I run it, it shows them litteraly and not the value stored in the tables thank you for your help!
OK, coming up with a title for this one was a little difficult. Almost as difficult as the problem I'm facing ;)
Here's the basic premise. The database is used to register people for a weekend convention. Part of the registration allows the person to request a specific roommate. In an ideal world, this would be relatively simple--match the roommate to the person, and you're done!
Did I mention they're teenagers?
Here's what happens. In all of the following examples, you are given only the ID number of a person to start with (because referencing any column but the bound one in a combo box from a query seems impossible, despite what Microsoft has to say (http://office.microsoft.com/en-us/assistance/HA011730581033.aspx)), and must dump all relevant names and ID's into a temp table.
Example 1:
IDFirstLastRFirstRLast
1MattBrownLukeWilson
5LukeWilsonMattBrown
9PhilHeartMattBrown
In this case, we have two people that have requested Matt Brown as their roommate. Based solely on the ID Number of any given person above I need to dump all three names into a temp table to be displayed to the user, so he can pick who will go where. Physically, the first two people would be roomed together, and the 3rd would be roomed in a single room nearby.
Example 2
IDFirstLastRFirstRLast
1MattBrownLukeWilson
5LukeWilsonMattBrown
9PhilHeartMattBrown
72CraigJohnsonPhilHeart
Similar to the last, but we've added a fourth person that has requested the 3rd person. Doing it the manul way, we'd room the first two together, and the last two together, in rooms next to each other. Unfortunately, we may not find the last two until we're two floors away, and end up having to shuffle people all over the place to get it to work. Now, we could be jerks, and just say, 'sorry, you screwed up, your problem, not ours'...except facing a crying 14-year-old girl upset because she's not with her friends isn't exactly fun. This is why I want to find all four people immediately after rooming any one of the above.
Example 3:
IDFirstLastRFirstRLast
1MattBrownLukeWilson
5LukeWilsonPhilHeart
9PhilHeartCraigJohnson
72CraigJohnsonMattBrown
Ah, the circle of life. Person 1 requests person 2, person 2 requests person 3, 3 request 4, 4 requests 1. This is surprisingly common. And annoying. Physical results would be the same as the previous example.
So to summarize, I start with person 1. Add him to the temp table. Then find the person he requested as a roommate. Add him to the table. Then look at the roommate's requested roommate, compare it to the temp table. If the person is there (ie, a direct match), then stop. If he isn't there, then query the database. Not there? Stop. Found another hit? Add him to the table. Search again. Then, display them all in a list box allowing the user to pick and choose who goes where.
I don't hope to automate the whole thing. That's not the goal. There are a variety of other factors that go into rooming that could never be automated, and require a personal touch. All I want to do is make it easier to find duplicates to streamline the existing manual procedure.
Now, how do I do it?
Hi I am running an Access program on a network. It's function is to grab some data including a Date and then mail merge it to a word template letter.
The 3 Computers are running WinXP with Word 2001 with Access 2001.
What is happening is that on 2 computers when a mailmerge is done they are displaying the date as reversed ie 9th Decemeber 2004 as 12th September 2004....I live in Australia so we support dd/mm/yy.
On the other computers it works fine.
Now interestingly if I change the data type in the table where the data is being grabbed from to a Text type instead of a Date type, the date is displayed corectly on the two computers that it wasn't working on before and incorrectly on the computer it was working on initially.
On all computers the regional settings are set to Australia.
Also within the word document I am formatting the dates to a long date in the toggle field code section ie;
{MERGEFIELD "DD" @ "d mmmm, yyyy"}
Any help suggestion will be very welcome as it is vital for me to run my program on all computers via network
Thankyou in advance
I have a main form and a several subforms. The main form displays potential customer data and 1 of the subforms displays the advertisement that prompted that customer to call. 1 of the Advertisements is newspaper ads and for that one we want to track which newspaper.
The subform has 3 pieces of data. The advertisement type, advertisement date and a hidden field for the customer ID to link those to the appropriate customer. There is also a requirement for a field for the advertisement source for newspaper ads.
Data Structure:
Customer Table
CustomerID
Customer demographic data as fields
Advertisementtype
AdvertisementTypeID
AdvertisementType
SpecificAdvertisement.
SpecAdvID
Advertisementtypeid
advertisementdate
advsource
sample data
Customer
1
John
Smith
1234 some st
somecity
somest
somezip
Advtypes
1 google
2 Newspaper
3 Radio
specificAdv
2
7/7/2015
Daily Press
I have a combo box that gets the advtype Then another combo box that gets the adv date the issue I'm having is I also need the source and If I put it in the combo box it only displays when that combobox has the focus otherwise only the date shows. If I try to use a textbox to display the data I can only get the firstcolumn in the combobox and not the column I want. If I use the same query the combobox uses but only having the source in the select criteria I get the same data for every record. Since there are multiple advertisements and a customer could reply to any of them or all of them.
Thought I best ask this question BEFORE I run into trouble.
If I were to set my database to hide the database window and all menus on startup so that users can only do what I want them to do and not edit anything. How do I go about reversing this if I needed to edit something as I would not be able to see any of the regular menus?
It would be good if the solution involved some sort of admin password rather than a sneaky short cut that a user may accidently stumble across.
I have 3 tables
tbl1 :
NameID
Name
tbl2 :
CatID
Category
tbl3 :
ProdID
CatID
NameID
Remarks
How can I avoid duplicate records in tbl3? Since remarks can be Null? product is later used in tblInventory...
I need to be able to assign a title to a set of dependents that is not what they are normally called. As in, every dependent is either called "Dependent" or "Step Child", but I need to call them "Child1" "Child2" "Child3" etc, for however many children an employee has.
I have a function that does this, however, I need the oldest child to be Child1 and so on, but every time I add an ORDER BY clause to my sql, it duplicates the records. I confirmed this by adding the child's name to the end, and instead of this:
Child1 Martha (eldest)
Child2 Edward
I got:
Child1 Martha
Child2 Martha
Child3 Edward
Child4 Edward
[Code].....
I have a cool little form for the use of the organization's treasurer wherein she enters checks and deposits. I have a query that generates a running sum, so each line in the accounting report based on the table has the account total as of that date. The query behind the report is exactly what I want and is in the correct order. For example, the four transactions for 9/15/2014 are in the order entered, and the line total is correct. The report insists on listing the 4 in the example in some other order with or without OrderBy specified. With code from someone, perhaps on this forum, I added the following to the OnPage event:
Code:
Private Sub Report_Page()
Const TWIPSPERINCH = 1440
' Offset from right edge of Control where our
' Vertical Line will start - adjust as you desire.
' Expressed in Twips
Dim intLineMargin As Integer
[code]....
The result is a beautiful report (albeit in the wrong order) with vertical lines all the way to the bottom of the page.If I remove the OnPage code, the report is still in the wrong order, so obviously that wasn't the problem. OK, I think it is corrupted. I open a new db, import all into it. Still in wrong order. So next, I begin to recreate the report from scratch. Hooray! Right order! But I still want the vertical lines between the columns. So now, I copy the OnPage code and paste it into the new report's OnPage code. I get error message: "Compile error: User-defined type not defined" with the line "Dim MIPSstr As udtPrtMips_str" highlighted. Ok, I delete all of it and reenter it line for line manually, leaving out the comments. Get the same error message. After trying to research it, the only suggestions were to add Activex reference which can't be the problem, because the other version of the report doesn't throw an error and both are in the same db.
I have attached a clip of the report and a clip of the underlying query. As you can see in the first capture.png, the check numbers are in the wrong order and the amounts in the Total line don't sync with the total above adjusted by the amount on that line.
How do I get VBA to say: Go to the first control in the tab index after the current active control (that is visible atm),
except such and such controls (because they will lose visibility in a moment).
I am a VBA beginner and am using Allen Browne's method for filtering a field in a subform: URL....It works well except that I cannot seem to figure out how to order the results when the subform field is filtered. I want to order the results by CompanyName.Here is the code I am using for changing the record source from the main form to the subform:
Code:
Dim sSQL As String
If Me.cboFilterFavorites = Null Or Me.cboFilterFavorites = "In Business" Or Me.cboFilterFavorites = "Out of Business" Or Me.cboFilterFavorites = "Unsure" Then
If Me.RecordSource <> "qryCompanies" Then
Me.RecordSource = "qryCompanies"
End If
[code]...
I feel as if I have tried everything. I even changed the subform record source from a table to a query (qryCompanyTypes) so that I could add the CompanyName field and sort but to no avail. I have tried adding the CompanyName field to the SQL string but keep getting errors. I have tried simply using Order By in the SQL and still get errors.
How to define Order that starts with a dependency on the existing value.
Basically, a function that chooses what Status Validation to run then knows when to stop that displays some very simple predictive Inference.
Each Record has a Status. The Status will generally be correct 85% of the time. Once in a while, various things in the database are updated that triggers the business need to change the status.
-A collection of Rule based validation functions matches each Status.
-The objective is not to run Rule 1 through 40 sequentially for each record.
-Each Rule Validation function is lengthy and can take 0.1 seconds per rule (per record).
-If the current record's rule is still valid 85% of the time, Then run that Status Rule Validation function first.
-If the Validation returns True, there is no need to run the rest of the rules. Move to the next record.
The challenge: Statistically, if the Current Status failes its Rule Validation, there is a high probability that one of the Status Validation Function near to the current Status will validate to true.
Like the board game Clue, once a Validation Rule comes out False, there is no need to check it for that single record again.
In an extremely simplistic world, think about Status of : Order in Progress, Order Completed, Completed Order Paid, Shipping in Progress, Item Shipped Ready, Shipped, In Transit, Delivered, Rejected, Payment Recended, ...
So, if the current status was In Transit, there is a higher odd that Delivered would be the next. There would be no need to start from the beginning again. The wonderful world of Metadata.
I have code attached to a command button to fill a Combo Box with data from a music collection. A letter of the alphabet is entered into a Text Box then records beginning with that letter are copied from a table, either by Artist or Title. They are saved to a temporary table at which time they are in no particular order. Those records are copied to a further table and saved in alphabetical order. This table is then used to fill the Combo Box.
I used two temporary tables because the records were not displayed in the correct order. I hoped this might cure it, it did not. The records are in order in the table but not in the Combo Box.
Code:
Private Sub Command68_Click()
'SEARCH AND FILL COMBO BOX
On Error GoTo errTrap
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tempList;"
[Code] .....
I have an Access table with transactions that were entered in random order. I need to recreate the table with those transactions entered in date order. I started by making a copy of the table (table structure only) then creating an Append Query sorted by the date field thinking that the records would be appended in the order created by the query. It did not work as I wanted. My next technique was to use VBA code to open a recordset that was created with the following SQL statement:
strSQL = "SELECT * FROM tablename ORDER BY datefield" & ";"
Set rst1 = db.OpenRecordset(strSQL)
The compile hung up, apparently not liking something about the SQL statement. I have done these things many times before during the last 15 years of using Access. I am about to shoot my computer.
what I am doing wrong?
I've got a rapidly expanding database that I designed to do my quotes for work and now I'm trying to expand it to add up the invoice amounts when the jobs come to fruition. I've got 99% of it working well but as I'm not the only one entering data I'm trying to make it as foolproof as possible and here is my problem.
I have a multi-line textbox that receives 'vehicles' in an abbreviated format and I need to get them in order based on a sort column number in the appropriate table. This i can do but the code I have found removes the duplicates which I don't want as I can can two identical vehicles on the same job.
e.g
Vehicle Sort Order
UNT/FL 500
FL4/45 400
E7/F5 600
UNT/FL 500
I want as
FL4/45 400
UNT/FL 500
UNT/FL 500
E7/F5 600
I have some textboxes that I want them to appear in datasheet view with a specific order.
How can I control the order given that I tried tab index and tab order but did not achieve the desired result?
Hi All,
I have a question:
I have 3 forms (a,b,c). Forms A and B are seperate forms and C will be activated by either A or B. The recordsource of C is a query which can be:
[forms]![formA]![text1] = Qr1
[forms]![formB]![text2] = Qr2
How can I write a code (or adjust it in the query) that that lets form C change it's rowsource in Qr1 or Qr2?
hi everyone,
i'm new to this forum.
I have a drop-down list that I want to populate from one of the columns in a table. But i want to do this with VBA, instead of building the query.
The reason for this is that I'm saving the project as an MDE file, and my database is residing in another *.mdb file. Thus the MDE file is just the executable version.
I can't seem to "read" the table without using VBA to do it.
Can anyone help me please?
thanks.
Hi,
I create the the combox in the form.
The Enter Event of the combox is
name.rowsource = "(select 'ALL' From [tbl]) Union (select [Name] from [tbl] Group By [Name] Order By [Name])"
The list solutions are:
Alan
ALL
Amy
Jeff
....
...
Can I put "ALL" in the first postion?
ALL
Alex
Amy
....
Because "ALL" means all the values.
I want to put "ALL" in the first place.
Thanks.