I know that i was discussed a lot but now it's about accexx2010.
I need to have more than 8 items on my switchboard but can't find any code for that in VBA.
I'd rather not use the option of building a regular form as switchboard.
I use the switchboard wizard to set up a switchboard. At the moment my main switchboard has 3 items and they are all set to 'open form in add mode' and they all point to the same form at the moment (which does exist).
When I try to go to form view of the switchboard I get the error 'The control name label1 is misspelled or refers to a control that doesn't exist'. Behind it I can see the form in form view and I can see there are the 3 menu items I would expect but when I close the error dialogue and the following 'close macros' dialogue' it goes back to design view. In design view I can only see one menu item (which seems to be a text box rather than a label) instead of 3. I changed it to a label but it didn't work.
Greetings!
I wanted to have a switch board available to my users. On the switch board I wanted to have a combo box where the users have multiple choices. For example, if a hotel was offering choices. The choices could be:
Room Reservation
Conference Room Reservation
Catering
Dance Hall
Transportation
Local Guide Information
After they select one of the choices, I wanted to put command buttons on the side that would take them to a form specifically related to the choice they made. So, some of the command buttons might be:
Send an Inquiry
Call
Make an Online Reservation
View information only
I haven't seen any Switch board that has choices so I don't even know if this is possible. If so, I'm happy to try.
Meanwhile.... I've never done VBA or any type of programming. I'm a beginning Access user.
Thanks.
Surfette
Here is an interesting one:
Have 2 forms, (1) Contact Information, and (2) Survey Response
Any Clues on how to set un a button to switch from Form to Form but stay on the same Record!!!! Interesting One!
Apriciate any help/clues/links =)
Hi Guys,
You know on forms, you have those little Left and Right arrows (< >) at the bottom of the form, and you can click on them to switch between records... yeah?
Well, how can I make the form switch between records with VB. Lets say I want a VB script that jumps to the 5th record in the table, how would I do that? Normally you would just click the Left arrow 5 times... but I want to do it with code.
Thanks!
I have a form called "Customer Details", on that form i have child forms, including one that has a schedule on.. so far everything seems fine, am able to get it to pull data correctly to the correct fields.. etc
I'm using 1 main table for all of this called "Cases"
Now the problem is, when i i click a button to print (this works so far), it opens another form and then fills in all the textbox's, however this is where i've got the problem, as it always seems to pull the data from the first record, not the record that i'm currently on..
so for example, if in the customer details, someone looks up "joe Blogs" using the navigation search and then wants to print it, by hitting the print button .. it should open another form, fill in the boxs and print "Joe Blogs", however it's always doing "Joe adams" - 1st Record
Is it possible to create a VBA on the AfterUpdate() that flips a value based on a condition?
For example, if in a text box (Gender) there is a value of "Male" and in a combo1309 the value is "No", can Gender be switched to "Female" automatically?
Code:
IF Me.combo1309 = "No" Then Me.gender....
I would like to create a command button on the header of a form. The button should perform a toggle switch: click once to activate the task and click it again returning original state. I can create the command button to perform a task but do not know how to return to original state. The button should have indicator showing which state is in.
View 2 Replies View RelatedI'm using a navigation control with 3 tabs. I would like to open a specific tab after the user login. I tried this but it does not work:
NavigationControlName.Tabs.Item("tabNameToOpen").S etFocus
I have tried:
Me.NavigationSubForm.SourceObject = "FormName"
then the form change but the tab selection no!
Hi all
I have been asked to build an in out office notice board that will keep track of all employees in the building. we use windows server 2k and I would like to record in/out in an access / sql database.
Any one know how I could go about this. Thank
Paul
Hello all, I am new to the site and have found so much useful information and examples but one thing I haven't found yet. I am interested in knowing can someone post up an append example. Here is what I am trying to accomplish. I have a database with two tables. The fields are identical and I can vew either one with ease. The problem is I would like to be able to copy the information from the current table to the second one with a push of the button. I can create a button with the help of the wizard that copies the record but it only copies the record to the current table which I have open.
Another thing is when I try to close access, I notice I always get a messagebox asking me about the information that is copied on the clipboard (due to me trying to copy a record). Is there a way to supress this message so people won't see it.
I know this alot to be asking and I just got here but I will be sure to help out other members when I have the knowledge of the program.
Thank you all for reading this long post!
Is there anyway to strip all characters except numeric out of a value? For example: 999/777-5555. I want to strip the / and the hyphen. The characters can vary so is there anyway to recognize just numeric?
View 13 Replies View RelatedThis is probably very simple but still beyond me.
I have a table that stores text in a number of different languages. Each entry has a unique ID number. Each language version of the same text shares the ID number.
I have a column of tick boxes that show where this text is used.
ie
1.0 English_text_record_1 tickcol1=y tickcol2=n tickcol3=y
1.0 French_text_record_1 tickcol1=y tickcol2=n tickcol3=y
1.0 Spanish_text_record_1 tickcol1=y tickcol2=n tickcol3=y
2.0 English_text_record_1 tickcol1=y tickcol2=y tickcol3=y
2.0 French_text_record_1 tickcol1=y tickcol2=y tickcol3=y
2.0 Spanish_text_record_1 tickcol1=y tickcol2=y tickcol3=y
I am trying to set the tick boxes so that, if I tick one in an ID range, all the rest tick on as well. ie if I tick record 1.0 English tick_col_2, then the French and spanish will be ticked as well.
Any suggestions will be greatly appreciated.
Andy
How can two cells be used to calculate any data within a row or column?
For ex. to calculate balance in a database?
I currently have a table with contact data set out like so (first few columns shown only, but about 10 in total);
ID Establishment Surname FirstName etc1 etc2 etc3
1 ABC Frost Jim data. data. data.
2 ABC Jennings Paul data.. data.. data..
3 XYZ Smith Dave data.. data.. data..
etc...
You will see that the first two contacts are from the same establishment, but the third is not.
I would like the table to be formated so that I have a single row for an establishment, but multiple contacts in the same row, i.e. as below.
ID Establishment Surname FirstName etc1 etc2 etc3 Surname_02 FristName_02 etc1_02 etc..
1 ABC Frost Jim data. data. data. Jennings Paul data.. data.
There are 16,000 records which I need to manipulate (roughly 3 contacts per establishment), hence I wondered if there was a was to do this, or maybe export to excel first, then manipulate and then re-import into an Access table.
I am very new to access and any help would be greatly appreciated.
Cheers,
Dear all
I wonder if anyone could advise?
I routinely have to manipulate antibiotic testing data as part of my job. The data takes the form...
OPIE idAntibiotic nameSusceptibility result description
ID1AmikacinSusceptible
ID1AmpicillinSusceptible
ID1CefotaximeSusceptible
ID1CeftriaxoneSusceptible
ID1CefuroximeSusceptible
ID1CephalexinSusceptible
ID1CephradineSusceptible
ID1ChloramphenicolSusceptible
ID1CiprofloxacinSusceptible
ID1ColistinSusceptible
ID1FurazolidoneSusceptible
ID1GentamicinSusceptible
ID1High level ampicillinSusceptible
ID1High level streptomycinSusceptible
ID1High level tetracyclineResistant
ID1KanamycinSusceptible
ID1Low level ciprofloxacinSusceptible
ID1Nalidixic acidSusceptible
ID1NeomycinSusceptible
ID1SpectinomycinResistant
ID1StreptomycinResistant
ID1SulphonamideResistant
ID1TetracyclineResistant
ID1TrimethoprimSusceptible
ID2etc
...which I have to crosstabulate, reorder, recode and place in a new table.
Currently I use two queries in Access to do this, but it takes a long time as there are invariably a million or so records.
I've tried to use VBA to speed things up, but with limited success.
Firstly I tried to do a cross-tab and append for each unique identifier...
Code:Sub PopCrossReorderRecode1()Dim db As DatabaseDim rsAbres As RecordsetDim rsCrosstab As RecordsetSet db = CurrentDb()Set rsAbres = db.OpenRecordset("SELECT [OPIE id] FROM Abres GROUP BY [OPIE id]")For Each Value In rsAbresSet rsCrosstab = db.OpenRecordset("TRANSFORM Max(Abres.[Susceptibility result description]) AS [MaxOfSusceptibility result description] " & _ "SELECT Abres.[OPIE id], Max(Abres.[Susceptibility result description]) AS " & _ "[Total Of Susceptibility result description] " & _ "FROM Abres " & _ "PIVOT Abres.[Antibiotic name];") DoCmd.RunSQL "INSERT INTO CrossReorderRecode ( [OPIE id], Tested, Ak, pA, Ctx, Ctr, Cfx, Cx, Cfd, C, Cp, Co, Fu, G, A, S, T, K, CpL, Nx, Ne, Sp, pS, Su, gT, Tm )" & _ "SELECT rsCrossTab.[OPIE id], rsCrossTab.[Total Of Susceptibility result description], " & _ "rsCrossTab.AMIKACIN, rsCrossTab.AMPICILLIN, rsCrossTab.CEFOTAXIME, rsCrossTab.CEFTRIAXONE, " & _ "rsCrossTab.CEFUROXIME, rsCrossTab.CEPHALEXIN, rsCrossTab.CEPHRADINE, rsCrossTab.CHLORAMPHENICOL," & _ "rsCrossTab.CIPROFLOXACIN, rsCrossTab.COLISTIN, rsCrossTab.FURAZOLIDONE, rsCrossTab.GENTAMICIN, " & _ "rsCrossTab.[HIGH LEVEL AMPICILLIN], rsCrossTab.[HIGH LEVEL STREPTOMYCIN], " & _ "rsCrossTab.[HIGH LEVEL TETRACYCLINE], rsCrossTab.KANAMYCIN, rsCrossTab.[LOW LEVEL CIPROFLOXACIN], " & _ "rsCrossTab.[NALIDIXIC ACID], rsCrossTab.NEOMYCIN, rsCrossTab.SPECTINOMYCIN, rsCrossTab.STREPTOMYCIN, " & _ "rsCrossTab.SULPHONAMIDE, rsCrossTab.TETRACYCLINE, rsCrossTab.TRIMETHOPRIM " & _ "FROM rsCrossTab;"NextEnd Sub
...but just got a run-time error 3251 or an error with the transform statement.
Next I tried to address each antibiotic at time...
Code:Sub PopCrossReorderRecode2()DoCmd.SetWarnings off' Delete the current contents of CrossReorderRecodeDoCmd.RunSQL "DELETE CrossReorderRecode.* FROM CrossReorderRecode;"'Populate OPIE id in CrossReorderRecode from AbresDoCmd.RunSQL "INSERT INTO CrossReorderRecode ( [OPIE id] ) SELECT Abres.[OPIE id] FROM Abres GROUP BY Abres.[OPIE id]", dbOpenTable' Run an update query for each antibiotic, putting the relevant code into the relevant field'AmpicillinDoCmd.RunSQL "UPDATE CrossReorderRecode INNER JOIN Abres ON CrossReorderRecode.[OPIE id] = Abres.[OPIE id] " & _ "SET CrossReorderRecode.pA = IIf([Abres].[Susceptibility result description]=""RESISTANT"",""A"", " & _ "IIf([Abres].[Susceptibility result description]=""INTERMEDIATE"",""pA"", " & _ "(IIf([Abres].[Susceptibility result description]=""SUSCEPTIBLE"","""",""."")))) " & _ "WHERE (((Abres.[Antibiotic name])=""AMPICILLIN"")); "'High level Ampicillin'etc etc
...and whilst this worked it took twice as long as the original query, which I imagine is do to its repetitive nature.
If anyone is able to point me in the right direction I'd be grateful. I'm relatively new to VBA and finding it useful and frustrating in equal measures!
Thanks,
Iain
I have two tables: products and orders.I would like to query the products only with orders using the product code and/or model number and join up all product and order information for further manipulation.
View 2 Replies View RelatedPROBLEM: String Manipulation
"Cleaning up Mom's Christmas address labels"
I need guidance on the best string manipulation functions (Instr, Left, Right) to cleanup my mother's Christmas address list of 300+ names.
I have successfully imported the text file into Excel and exported to Access; fieldnames: FULLNAME, ADDRESS, CITYSTATEZIP
I have found instructions on how to breakout FULLNAME field into FIRSTNAME and LASTNAME.
But within the FULLNAME field are many combinations of titles (Mr., Mr. & Mrs., Dr., HON.) with inconsistent periods applied.
Which one of string manipulation functions:
Instr
Left
Right
would be best for extracting these various titles from this name field?
I understand the concepts behind the above functions, but not enough experience using them to understand the tedious syntax or which string manipulation function would be best for extracting the varying title entries to a separate created field called TITLES.
So far, I have deduced this will be a multi-step process. But asking for guidance:
1.) Which string function is best suited for this?
2.) Example of the function syntax for an update query?
2.) Suggested order to administer update queries?...
to extract misc titles from the FULLNAME field.
I am a novice-casual Access user.
Thanks, Greg
(If someone would copyright these steps into a book called "Cleaning Up Mom's Christmas Address List"... I am sure they could retire from sales on Amazon. :-)
I think I know the answer but want to check. I've been asked to create a query, without querying a query first, but it's the only way I know.
I have two tables
Table1 will have data in a column that is 9 characters long
ULCABC123
ULCABC124
ULCABC125
PLTABC123
PLTABC124
Table2 will have data in a column that is 6 characters long
ULCABC
PLTABC
Question: Can I create a Join from Table2 Field with the Left(Field,6) from Table1
I was thinking something like this. (but then I can't enter design mode)
Query1 - Test
Code:
SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Table2 INNER JOIN Table1 ON Table2.ORDDETTYPE = Left(Table1.ORDERDET,6)
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
I presume the only way to do this is first query Table1 (and call Query2) and return the first 6 characters and the create another query (Query3 in this case) using Query2 field joined with Table2 field.
Query2
Code:
SELECT Left([ORDERDET],6) AS NEWORDDET
FROM Table1;
Query3
Code:
SELECT Table2.ORDDETTYPE, Table2.DESCRIPTION
FROM Query2 INNER JOIN Table2 ON Query2.[NEWORDDET] = Table2.ORDDETTYPE
GROUP BY Table2.ORDDETTYPE, Table2.DESCRIPTION;
I'm trying to parse the following into an Array by splitting the csv file using a "," comma separator. There should be 63 different data pieces in this File. When I do a count of them from the (ubound array) i only get 54. The last data piece on each row gets concatenated to the first data piece of the next line. Is there a way to stop this from happening? This is causing problems with working with the data.
Date,Open,High,Low,Close,Volume,Adj Close
2013-06-07,1625.27,1644.40,1625.27,1643.38,3371990000,1643 .38
2013-06-06,1609.29,1622.56,1598.23,1622.56,3547380000,1622 .56
2013-06-05,1629.05,1629.31,1607.09,1608.90,3632350000,1608 .90
2013-06-04,1640.73,1646.53,1623.62,1631.38,3653840000,1631 .38
2013-06-03,1631.71,1640.42,1622.72,1640.42,3952070000,1640 .42
2013-05-31,1652.13,1658.99,1630.74,1630.74,4099600000,1630 .74
2013-05-30,1649.14,1661.91,1648.61,1654.41,3498620000,1654 .41
2013-05-29,1656.57,1656.57,1640.05,1648.36,3587140000,1648 .36
I have several thousands of lines of data which I wish to manipulate programmatically, if at all possible. I think that all of the possible permutations are summed-up by the following examples :
123 A text string
2-8 Another text string
A-C Another text string here
3-20 And some more text
3A-126B More text
Some text without any numbers or letters at the left
What I need to do is :
Move the alphanumeric data at the left to the right. So the data would end up looking like this :
A text string 123
Another text string 2-8
Another text string here A-C
And some more text 3-20
More text 3A-126B
Some text without any numbers or letters at the left
I suppose it hinges on identifying where the first space in from the left appears, cutting the string at that point, and
Is this possible, programmatically ?
While processing string manipulation on a table (140K records, 200-250 MB) the file has corrupted, and I lost all vba Modules, but the data and other DB objects seemed to be in tact.
I now have a query with a text field, when I make a simple join SELECT, the text comes in fine (and, of course, seems fine when presented in table), but when Group by - I get Gibarish: "CARVEDILOL 6.25MG, TABLETS"
Is presented in Group by as: "砅5"
I have recreated the file, importing queries, including this one, but then recreated it as a totally new query, but get that same results.
File Corruption? anything to do about it?
Hi All,
Some help from cyber space is what I need me thinks :confused:, I am using a switch function in an access query to convert 24 to 624 and 28 to 628 and so on for about 5 entires however there are numerous entries that might not meet my criteria that are errorrs can I just simply place in there a default option if the rest are not made like in a case statement.
I know somebody is going to suggestion placing this in a macor/function but would prefer to not have to do that if possible, but if I do I dot.
Any help greatly appreciated. Come on Cyber Space dont let me down! :D
Cheers,
Tempest
"Life is like a box of choclates you never know what your gonna get"
I'm using the simple test expression below to assign a value of 1 to records with [saledate] between the two dates indicated in the code below and I'm just getting a null result. Can the between function not be used in this way?
Code:
Hughes Test: Switch([saledate] Between 1/1/2014 And 3/14/2014,1)
i'm trying to use a switch function in a query but i'm unfamiliar with the format a bit. here is what i'm trying to do...
switch([Radios]![Warranty] and [Radios]![HousingWarranty]=true,[Radios]![Change front housing]*(25/60*28), [[Radios]![Warranty] And [Radios]![PortableHousingWarranty]=true], [Radios]![MaterialsUsedPrice1])
basically first statement is condition second is execution. I need to add more but i'm getting an error from this much...if I was doing this in vb.net I would just do if, ifelse, ifelse, ifelse...but...
Thanks,
I am trying to use a Switch expression as a column heading in a crosstab query to generate statistics for how much work is done per client (Requester). Each requester is referred to by acronym, so it may look like "DOD/OUSD/FTT/ATA/B", but I want that column to include everything that starts with DOD, instead of listing each instance separately. This is my expression so far:
Expr1: Switch([Requester]="DOD*","DOD",[Requester]="DS/ATA","ATAP")
When I run the query, the column "ATAP" shows up perfectly with all the correct info, but it's as if I never entered "DOD*". Nothing appears, and since I didn't add anything in the Switch function for what to do with other requesters, the second column shows up as <>.
Do wildcard *'s not work in Switch functions? I don't get any errors, but this is definitely not the information I'm looking for.
Is there any other way to include all Requesters that start with DOD in one column?
This is my SQl if that's helpful:
TRANSFORM Sum([Q and D Database].[Word Count]) AS [SumOfWord Count]
SELECT [Q and D Database].[Source/Target]
FROM [Q and D Database]
WHERE ((([Q and D Database].[Out Date]) Between #1/1/2006# And #12/31/2006#))
GROUP BY [Q and D Database].[Source/Target]
ORDER BY [Q and D Database].[Source/Target], Switch([Requester]="DOD*","DOD",[Requester]="DS/ATA","ATAP")
PIVOT Switch([Requester]="DOD*","DOD",[Requester]="DS/ATA","ATAP");
Thanks!!!