Modules & VBA :: How To Find A Table Name Containing Particular String
May 21, 2015
I have a command button on a continuous form(form 1) and I need this button to open another form(form 2) when I press on it. So far so good.
When I press the button, I need some VBA to open the form(form 2) , search for a particular table name based on the open form(form 1) current record and use that table name as the newly opened form (form 2) data source. I have ways to do most of those task but for one thing:
How do I make access search for a table name containing a particular string? Here's what I am working with:
Code:
Private Sub Commande26_Click()
On Error GoTo Err_Commande26_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim stDataSource As String
[Code] ....
View Replies
ADVERTISEMENT
Aug 29, 2014
I've got a string variable with a value that could be typically
"ABCD|123|R"
The string needs to be split into its three parts, the pipe symbol being the separator. Then the middle numeric string must be converted back to a long.
I know i could do a loop which identifies each character in turn, but my question is:
Is there a VBA function that can pick out the position in the string of the "|" characters, so i dont need a loop ?
View 3 Replies
View Related
Oct 7, 2014
I am looking to find if a field contains a number and then build a case statement depending on which number is found. The field will contain data just like this:
Quote:
Repaired frequency response and grounding issues. Replaced 2 Hybrids, capacitors, and connectors. Tested MER/BER and operation to specs.
Here is my code that did not work:
Code:
If Me.txt_work_comm1 Like "*Hybrid" Then
'Sets up auto priced based on number of hybrids entered
'1 hybrid
If Me.txt_work_comm1 Like "*1" Then
strCriteria = "repair_item = 'Charter RF Amplifier Repair + 1 Hybrid' AND profile_types = 'Alpha'"
[Code]....
To Summarize:
1. I need to find if the word "Hybrid" or "Hybrids" is in the field
2. Then I need to know how many to determine a price
View 1 Replies
View Related
Apr 21, 2015
I need to find the respective numbers for a textstring when for
abcdefghijkl stand the numbers
79 81 82 83 84 85 86 87 88 89 91 92
The textstring to "decode" is for example is 'adgjk'
The result (79 83 86 89 91) should be added into a table by Looping.
rs.Addnew
rs("Letter")= myarray??
rs("corNumber")= myarray?
rs.update
rs.movenext
Something like this.
But I cannot define and Setup the Array, which should be the best way for doing this.
The Array does not change its Content nor its Dimension.
Both, letters and numbers are strings.
View 14 Replies
View Related
Dec 2, 2013
I'm trying to search a for string within a subform to find information stored on the mainform to which the particular subform belongs.
The problem is that the subform is generated from a query which uses a number from the main form to generate.
So the subform record is only generated when the correct mainform record associated with it is loaded.
Now to solve my problem I've made a new query that brings up ALL the results that could be generated by the main form and from that I can search to find my search term I'm after and read off the ID number to tie it back to the mainform.
But all of this is done manually, I want a way to do all this using VBA in a way that the user can't edit any records as they are doing it.
View 3 Replies
View Related
Mar 21, 2014
I have a form with a listbox that displays the name of a table. Once the listbox item is selected, the table name is set to a variable called myFile. I want append the records from the table (myFile) into another table.
View 1 Replies
View Related
Sep 9, 2014
In my database, I use TextStream.ReadLine to read a .txt file line by line and store pertinent parts of each line into specific fields in a table. One of these fields is called "Remarks", which is basically a descriptive paragraph of text explaining a task. Everything works great so far.
However, my leadership would like a condensed version of the "Remarks" field. The only way to really do this right now is for someone to manually read each "Remarks" field and create their own like condensed version of it. Let me give you an example...
Remarks: "Conduct Project Delta tests in association with IBS/SCADA systems and CIKR (Critical Infrastructure/Key Resource) cyberspace terrain, develop CPT certification processes and checklist."
Condensed Version: "Project Delta for IBS/SCADA and CIKR"
I've played with using Select Case to automatically create a condensed version of the Remarks field:
Code:
Select Case True
Case InStr(strRemarks, "SCADA") > 0 and _
InStr(strRemarks, "Project Delta") > 0:
!Condensed = "Project Delta for IBS/SCADA and CIKR"
End Select
However, this is too much VBA maintenance for each different thing that needs condensed. If a new tasking comes out, then I'll have to go into VBA and custom create a new Case for it. Multiply that by 10-20x each week.
Instead, I would like a form where my users can specify the criteria themselves. The Remarks field would be compared against the criteria to create a new condensed version of the Remarks field. I'm not sure how to go about this though. What I'm envisioning is this...
So each Remarks string would get compared against each criteria. If the Remarks string contains the words "SCADA" and "Project Delta", then the condensed version would be "Project Delta for IBS/SCADA and CIKR". If the Remarks string contains "OPSEC Assessment" then the condensed version would be "OPSEC Assessment". In the pic above, the form is based of another table that contains those fields in the form. I'm not sure if this is the most efficient method for my goal or not. Either way, I don't know how I would compare the Remarks string to records in this new table in order to create a condensed version.
View 14 Replies
View Related
Feb 26, 2014
I have a table with a field that contains IDs, e.g.
123
456
789
I would like to generate a single string from this table, seperated by commas, e.g. 123, 456, 789 and output to a field in an existing table. This will then be used in a SQL statement.I am new to VBA and don't reallly know where to start/
View 9 Replies
View Related
May 9, 2014
I'm trying to create a temp table, which is populated by a string, (taken from a recordset).
My problem is incorporating the String into the SQL statment, and making it work,
What I'm trying to do is to create a temp table, and populate it with the first record of the recordset, (which is an e-mail address).
(The recordset and the strings work fine). It's the SQL statement which doesn't work.
Here's the code I have :
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~
Set DBS = CurrentDb
Set RST = DBS.OpenRecordset("SELECT Contact FROM Contacts_to_be_Mailed")
Dim My_Count As Long
Dim ContactString As String
RST.MoveFirst
ContactString = RST(0)
Dim strTable As String
strTable = "TempContact"
DoCmd.RunSQL "INSERT * INTO " & strTable & " FROM ContactString "
View 1 Replies
View Related
Jun 7, 2013
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
View 13 Replies
View Related
Aug 23, 2013
I have a database for work where I have a table of meters and a table of Faults which has a list of all faulty meters at one time. When a fault is repaired, I have a macro which updates the Meter Status to Working, adds a Fault Closed date, appends the record to the Closed table and then deletes it from the Faults table.
The user runs this from a form by clicking the Closed Fault button which activates the macro. I've added Echo on and off to hide that the form is temporarily closed while the Append and Delete queries are run and then it is re-opened again.
My problem is that the Form always opens at the first record in the Faults table. I would like it to open to the record which would have been next after the one that has been moved to the Closed Faults table.
Below is the code I have been using to test the Copying Meter Reference, closing and opening of the form and finding the correct record:-
Function CopyTest()
On Error GoTo CopyTest_Err
Dim strMeterRef As String
DoCmd.SetWarnings False
DoCmd.GoToRecord , "", acNext
strMeterRef = Meter_Reference
[code]....
As you can see I am trying to go to the next record, copy the Meter_Reference by setting it to strMeterRef and then Find strMeterRef when the Faults form is re-opened.
I have a Macro embedded in a button which calls the above Function by using RunCode but nothing happens.
View 2 Replies
View Related
Jan 29, 2014
I've a code that can split strings. but now i want the last thing he split in a string named "name".
The string what is split is folder places like C:Usersinengine@mccain.comDocumentsTestvan e st.txt.
Now I want that text.txt comes in 'name'
View 2 Replies
View Related
Sep 3, 2014
I am trying to return a single value from a table and assign it to a string to be used later but Dlookup isnt working at all. below is the code im using and the error message im recieving is "wrong number of arguements or invalid property assignment"
Code:
Sub boo()
Dim result As Integer
result = dlookup("Definition", "Config", "Parameter = 'Mail Folder'")
End Sub
View 13 Replies
View Related
Nov 1, 2013
I am tying to query the Symbol table to see if a recod exists with symbol code.
I am querying the tblSymbol table from Excelk vba and the Access DB is on my machine.
The code I am using is:
Set rs = New ADODB.Recordset
rs.Open "tblSymbol", cn, adOpenKeyset, adLockOptimistic, adCmdTable
Set rs2 = New ADODB.Recordset
sql = "SELECT * FROM [tblSymbol] WHERE [SymbolCode] = """ & someSymbol & """"
rs2.Open sql, cn, adOpenDynamic, adLockOptimistic
[Code] ....
MsgBox Exists always returns -1 but i know the code exists in the table.
View 5 Replies
View Related
Jun 26, 2015
I have an MS Access 2010 db that has a main form called switchboard. This has 4 command buttons that open diffrent forms. Also on the main switchboard form i have an unbound textbox called TxtUserName that captures the users environ"username" when the switchboard form is opened.
I have a table called "tblAccessUsers" that i manually enter who i want to use my db. This table will have up to 50 names added to it. Their is only one field name in this table and it is "User Login".
When the user hits any of the commandbuttons on the main switchboard form i need some code that will look at the value in TxtUserName and loop through tblAccessUsers for an exact match. If it finds a match then it will carry out the open form command or if not prompt the user with a message box.
My knowledge of Access and especially VB is quite limited. I managed to create this using a DLookup but that only returns the first record in the table. The logic works but it will not look past the first record.
View 3 Replies
View Related
Jun 6, 2014
I have a table called login and inside that table is three columns: username, password and admin.
I have the username saved in a global variable called GsUser. How can i find the record in that table with the same Username as the string stored in GsUser and use that record for an if statement which sees if the value of the admin column is "Yes". Im trying to do it using VBA. Im not using a form where everything is bounded.
View 2 Replies
View Related
Nov 6, 2013
I'm trying to find all the table names inside an external access file from a path.
I have a code for the user to select a file which return a patch to the file.
Then I need to find all the tables names from that file and append them to a list.
so I them can input them into a code and link the tables.
here is the code to get the file patch:
Code:
Function getFileName(path) As String
Dim f As Object
Dim varFile As Variant
Dim path
Set f = Application.FileDialog(3)
[Code] ......
View 8 Replies
View Related
Jul 14, 2014
I'd like to expand me tree view automatically when a node matches a string variable from a table. In other words, when a node in a tree matches the given name (variable), the tree will expand all the way down to that name. I guess I need to use "For each node" and when the node and variable match, the tree structure will show up.
I have come up with something like this:
Code:
For Each nd In Me.tree.Nodes
If nd = level1 Then
nd.Child.EnsureVisible
Exit For
End If
Next nd
Me.tree.SetFocus
But this only shows the first level of my tree ( btw. I have a 3 level tree). I get lost inside the FOR when I want to make use of another two variables - level2 and level 3
View 1 Replies
View Related
Mar 21, 2013
I need a query to find all the field header names that contain string "PL-" and along with records contain a value with the field name containing "PL-" within a Access DB table.
View 3 Replies
View Related
Dec 3, 2013
I have an App that runs a few action queries using:
Code:
CurrentDb.Execute "[My Query Name]"
At some point I get this Error: The Microsoft Access database engine cannot find the input table or query <name>. Make sure it exists and that its name is spelled correctly. (Error 3078).The query is there, I can run it from the DB objects window.Queries run using CurrentDb.Execute earlier in the code.
View 3 Replies
View Related
Oct 31, 2013
my issue is i have multilble text box in my form & based on change in one of this text box i need the code to compare between data in the form & table & returm Msg if it is not matching. attached screen FYI.
i look in the internet but i could not figuer out the VBA code since i do not know VBA. what comes to my mind to to use select case.
View 14 Replies
View Related
Sep 5, 2013
I have some vba where I'm importing a text file and splitting it out into a table. For the most part its working just fine.
But I have a line of data that I need to pull out the string right after "Old" - Murphy and right after "New" ZMurphy
Acc# : 111111 This is test data, Person : 22222 Old Murphy New ZMurphy
I'm thinking Instr() could do this but I'm unable to get it to work.
I am using Access 2010...
View 3 Replies
View Related
May 1, 2014
I'm trying to get a value from a spreadsheet to import into my MS Access database. Currently I am trimming the spaces/carriage returns from it but need to strip some more data from the value.
Here is my code.
trimmed_department = Trim(Replace(new_department, vbCrLf, ""))
Example value being "123 Point 5 Finance and Accounting"
I want to use Mid (I think?) to remove the "123 Point 5" (it is always the same with no exceptions) but don't know how to use it as I don't know how to use multiple parameters within a string.
View 8 Replies
View Related
Feb 27, 2014
I want to add a string as year to a date.
Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.
Code:
Public Sub BEN()
Dim strSQL As String
Dim t As Date, s As Date
DoCmd.SetWarnings False
Year = Right(pricedate, 4)
t = 1 / 1 / " & Year & "
s = 12 / 31 / " & Year & "
strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
" FROM TRANSFER_PRICES " & _
" Where [Valid_from] = " & Format(t, "#mm/dd/yyyy#") & _
" AND [Valid_to] = " & Format(s, "#mm/dd/yyyy#")
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
View 3 Replies
View Related
May 19, 2015
I have set calendar control 12.0 up and everything works how I want it to (click date and peoples names in a table to the left to show scheduled meetings on that day). What I want is to add a string on top of the calendar. For example, this monday I would like for it to say "Memorial Day" on the physical calendar itself.
View 3 Replies
View Related
Mar 24, 2014
I would like to cut off the first 6 and last 2 Charaters in an after update event but not sure how, I cannot use mid as the length of the string may change but never the first 6 or last 2, can some one show me how it's done ...
View 4 Replies
View Related
