Modules & VBA :: Drop Table Using If Argument
Oct 1, 2013
1) I want to figure out how I can drop a table using "if argument". If the table exists then drop table, if not exists do something else.
2) I want to figure out (ALSO) how I can drop a column using "if argument". If the column exists then drop column, if not exists do something else.
I wrote something but is not enough..I can't find more clear information about that.
Function first()
On Error GoTo Macro1_Err
Dim BD1 As Recordset
DoCmd.SetWarnings False
Set BD1 = CurrentDb.OpenRecordset("BD_example table")
[Code] ....
View Replies
ADVERTISEMENT
Jul 7, 2015
I had 3 drop down filters that worked when the word being filtered was written in the table. I have since then linked them to new tables to be more database-y. The problem I have found (a couple weeks later too) is that my drop downs broke.
It makes sense why, they are now 1,2,3,4 instead of words but how do I make this work now that it isn't in the current table?
Code:
Private Sub cboMDS_AfterUpdate()
Dim myPlane As String
If Me.cboMDS = 0 Then
myPlane = "Select * from Baseline"
[Code] ....
View 10 Replies
View Related
Jun 6, 2014
I'm having a problem with argument in custom function..My table is like this:
myValues
123.5
32.7
65.8
11.1
What I want to achieve is to multiply each value with the average of all values, so that at the end I get (avg(myValues) = 233.1):
myValues2
7196.963
1905.593
3834.495
646.8525
For this I would like to have a function (this is simplified example of bigger problem, that is why I need function, otherwise simple SQL would suffice as mentioned below also), so that I could do something like this:
'SELECT TestFn([myValues]) as myValues2 From MyTable' or
'SELECT TestFn("[myValues]") as myValues2 From MyTable'
and I should get the table above.
My code is like this
Code:
Public Function TestFn (FieldName) as String
Dim myAvg as Double
myAvg = Davg(FieldName, "myTable")
TestFn = FieldName * myAvg
End Function
I understand the problem is that Davg needs string, but if I change to string then the multiplication does not work. The 'FieldName' parameter does not have a Type specified because that is where the problem is if I give string Avg works ,but calculation does not. If I give Number, avg does not work.
P.S. "as String" at the end of function is not a mistake, I need number as a string
P.P.S I did not inculde NZ either as in my example it can never be Null.
View 8 Replies
View Related
Jan 13, 2015
I have just added a function to a database to strip out any commas or quotation marks from a passed string.
However, it is returning a black string even though when I step through the function it creates the out string correctly and the variable "OutString" is populated when the Exit Function command is executed.
Calling code.
InString = Btxt
OutString = ""
Call Strip_String(InString, OutString)
SBtxt = OutString
[Code] .....
View 4 Replies
View Related
Feb 23, 2014
I have a form with a button to print preview a report. This report needs one input parameter before executing. The computer this will run on is a touch screen and does not have a keyboard. Windows 7 has a "on screen keyboard" program. I want this to run first so that my user can input the parameter.
I have the following which throws an "Invalid procedure Call or Argument"
Sub CallTeclado()
Dim RetVal
RetVal = Shell("c:windowssystem32osk.exe", vbNormalNoFocus)
End Sub
View 4 Replies
View Related
Oct 22, 2013
I am designing a Public Function F()
I want this function to populate value from a any field selected from any table to any text box in any form ...... Lets say in a Database named TestDB we have a Table named tblTest , a Form named frmTest and in this form( frmTest ) we have one Bound Combo Box named cmbTest and one unbound TextBox named txtTest
We assume that the table tblTest has three fields : TestID , FName and LName .
We also assume that there are already some records in the Table tblTest .
If the function F() is already programmed it should take as arguments as it follows :
F(FormName as??? ,ControlName as ???, TableName as ???, FieldName as ???, ID_Field_Name_of_the_Table as ???, Combo_box_selected_ID as ???)
In result the function should (probably) DLookup (FieldName , TableName , ID_Field_Name_of_the_Table = Combo_box_selected_ID ) and then store the value in a variable ( probably Variant ???) , lets say called varSetValue.
The problem is IF this is the correct way to handle that issue , than what should i do next.... I really don't know how to pass a Control's name to a function from the Event module of the ComboBox -
I mean - how can I obtain the value so it is usable for the function .... What data type should be the function arguments so I can use them to set a value for a control in any form .
In example : If the Dlookup() is somehow successfull then I want to assign the varSetValue to the txtTest what should I do : FormName.ControlName.Value = varSetValue >??????
I have read a lot materials but couldn't find a good answer for that , and aswell what data type should be the arguments that the function accepts and how do I obtain Controls and Forms names so I could use them in the function as described above .
View 4 Replies
View Related
Feb 6, 2015
I'm developing an application where I want to call the keyboard up on the screen when a user enters a field. This is my setup:
Windows 8.1 32 Bit, Access Runtime 2010.
Exact lines of code are:
Dim RetVal
RetVal = Shell("C:Program FilesCommon Filesmicrosoft sharedinkTabTip.exe")
These lines of code work perfectly fine on my development PC which is running Windows 7 64 bit, Access/Office 32 bit.
I know the path to the exe is good. I can navigate and double click it and it works great. but the shell command is resulting in the invalid procedure.
View 4 Replies
View Related
Mar 25, 2014
I found this code on a website that uses a form to search all tables in my database. Problem is that when I click "search" I get an invalid argument error. I am guessing that there is a problem with my SQL string. Missing space? Missing quotation marks? etc etc.... Anyway, here is the code:
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset
Dim strSearch As String
Dim strTableName As String
[Code] ....
View 1 Replies
View Related
Jun 28, 2013
I want the MessageText argument to be autofilled with whatever value the combobox in the form holds. This is my code so far...
Code:
Private Sub Command54_Click()
Dim EmailText As String
EmailText = "Submitted By: " & vbNewLine & vbNewLine & "Description: " & vbNewLine & vbNewLine & "Subject: "
If Combo18.Value = 1 Then
DoCmd.SendObject acSendNoObject, , , "knagel@durablepackaging.com", , , , EmailText, True
For instance, after "Submitted By: " I want the value from the combobox: Submitted By in the form to be automatically placed there.
View 2 Replies
View Related
Jul 31, 2014
Why I am not able to get all records by excluding the Numrows argument in the getrows method?
Sub Test2()
Dim myrset As Recordset
Set myrset = CurrentDb.OpenRecordset("SELECT * FROM Holidays;")
myrset.MoveLast
myrset.MoveFirst
MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows(myrset.RecordCount))
MsgBox Excel.Application.WorksheetFunction.Networkdays(#8/1/2014#, #8/31/2014#, myrset.GetRows())
End Sub
Second MessageBox is giving a wrong value.
Is it a mandatory one? Or Do I have to do some ritual like (Movelast) before that?
(Holidays table is just having the values in the array only ie. #08/15/2014# and #08/29/2014#)
View 10 Replies
View Related
Aug 12, 2015
I keeping getting the error "Invalid use of Null" when i call my email function using optional parameters.
Code:
Email Me.ProjectID.Column(1), _
Me.ProjectAddress, _
Me.TaskDescID.Column(1), _
Me.TaskDescExt, _
strStatus, _
Me.TaskStatusID.Column(1), _
[Code] .....
View 4 Replies
View Related
Aug 21, 2013
As I am moving through my code, I'd like there to be a pop-up box which asks the user to choose from a list of dates. This list of dates only resides in a filtered recordset in the background.
Once the date is chosen, then my code continues onward..I know I could make some sort of pop-up form, but I'd rather not have to go in a design all that just for a list of dates...
1) Is there such a thing as a dropdown list on a Input box?
and even if there isn't...
2) Is there a way I can bind the column in a recordset to a dropdown list?
View 2 Replies
View Related
May 3, 2014
I want to open the dropdown box on my datasheet view subform from the main datasheet view form when I run over it with the mouse.From my main form textbox [WelderID]I tried this code but doesn't work. I get the error invalid reference to the property Form/Report.
Private Sub WelderID_MouseMove(Button As Integer, Shift As Integer, x As Single, Y As Single)
Forms![Main]![WorkOrder Form]![DrawingNo fm]![Welds fm]![WelderStamp Sfm]![WelderStamp].SetFocus
Forms![Main]![WorkOrder Form]![DrawingNo fm]![Welds fm]![WelderStamp Sfm]![WelderStamp].Dropdown
End Sub
I am not sure if there is more code to use to open the subform in the datasheet view.
View 2 Replies
View Related
Jan 5, 2015
I have a form with First name, Last name, Employee number, Information. The information box is a drop down menu with four options (contact, emergency, company, personal) I would like to be able to enter in search criteria for an employee using one of last name first name or employee number then choose an option from drop down box click a button to search and have it pull up just the info I needed.
I think I have two options but i am not sure which would be more efficient.
1. creating a query and using the criteria from design mode to have it filter the information. The only problem with this one is I do not know how to set it up with the drop down box from the form. Or would I need multiple querys for each option or just one with all of it?
2. writing code for the search button on the form that will bring up the appropriate query (I think I would then have to create a query for each drop down option)...
View 1 Replies
View Related
Apr 17, 2007
I have a db that had to be designed taking into consideration a previous db. Merging went well. On several of the forms that feed the tables there are drop down boxes for entry consistency. Those drop down boxes have two columns. Ex is Counties field: Column 1 = County Name Column 2 = Numerical Designation.
On the form the dropdown shows the name for selection.
On the table it is a different story. On the portion of the table that came from the old db the field shows the Name. On the portion of the table that pertains to data entered since the new db, only the number shows. This also means it won't let me search by county name - only by number which doesn't work.
I have tried to compare properties for the old and new database for the forms, the tables, etc. and can't find a discrepancy.
1. How can I get the field in the table to show only the name and not the number?
2. Will that solve my search problem?
Oh, does it make a difference that there isn't any sort of pk or fk in the lookup table?
Thanks.
p.s. I am learning as I am going - so really complicated answers are difficult for me
View 1 Replies
View Related
Nov 10, 2014
I am in the process of trying to create a DragNDrop feature in the Access database that I've built. I have 3 employee with several different txt boxes all side by side and I need to be able to drag and drop the job from one to another.
I need to create a class module to do this but my problem is I don't know how.
View 7 Replies
View Related
Jan 11, 2007
Hi all,
have used the wizard to take values for a table dropdown field from another table
the table has
surname forename and class all of which reside in another table... ive tried to set up the bound columns so when you select a surname from the drop down the other two fileds also referencing that 6able are automatically filled in with their corresponding values.
so if the ref table had
smith john 1m
by selecting smith in the new tables drop down, the forename and class fields automatically select the corresponding values from the ref table..
im sure this is possible but cant get it to work and cant find anything on the web (although this is probably down to not knoing the right search string)
any ideas?
dubs
View 5 Replies
View Related
Jul 31, 2006
Hi,
I have a java app connected to a MS access database. I need to run an sql command like
"drop table if exists"
You know, so it only tries to delete the table if its actually there. Im not sure how to do this with an access db.
Also what is the difference between delete and drop.
thanks v much for any help!
View 1 Replies
View Related
Aug 9, 2006
I know this can be done i MySQL and with SQL Server but is there a way to drop the table but check if it exists first in Access before running a query ?
View 1 Replies
View Related
Jul 24, 2012
Is there anyway that access could create a drop down box within a drop down box? For instance, when a selection is made in a drop down box, it opens another drop down box with choices.And is it possible to link an inserted image from a form to open in MSPaint that when it opens, the image is already there?
View 1 Replies
View Related
Feb 15, 2006
Hi All,
I am wanting to build a table which consists of all the queries and tables in the database.
I am expecting column 1 to have all the queries and column 2 to contain all the tables that make up the query. So the table will look like this:
Queries - Linked Tables
Query1 - Table1
Query2 - Table1
Query2 - Table2
Query3 - Table1
Query3 - Table4
As you can see a table can be linked to any number of tables.
Now for my actual question. I don't want to have to type in all the queries and tables. I would like to choose any query and table from a drop down of all possible queries and tables that are currently in the database - like a list, where the list contains all the possible values.
I would then select the relevant entry.
Any pointers would be great.
Thanks,
Evan
View 2 Replies
View Related
Jan 26, 2008
It is essential that i use MS access to do this, what i need to do is have a drop down list on an ms access table to make sure the data in the field is exact, i could use validation if this is not possible but i would prefer this method also i dont need an answer to this but is there a way to make the key field dependant on whatever is selected on the dropdown menu? sort of use a general input mask but alter something dependant on the drop down menu to have it as specific characters, i have some experience with VB if there is a way to link vb programming with access
View 6 Replies
View Related
Feb 27, 2014
I was browsing the Northwind database and notice that there is a dropdown in the table (See attached print screen). I've been trying to recreate it but I'm having a hard time.
View 2 Replies
View Related
Sep 12, 2014
I would like the user to enter information directly into an Access table (it's ok). I want one column to have month ended. In this column, there would be a drop down/lookup menu with each month of the year. The user selects the month we are being billed for.
Then another column called week ending date, would display all week ending dates (ends on a Sunday) but only for the month that was selected.
If the user selected another month on another row, the available weekending dates to select from would show only what pertains to the month selected. Is it possible to do this?
View 2 Replies
View Related
May 12, 2006
Hi,
For some reason I cant see the drop zones when I am pivot table view. Does anyone know maybe why I cant see this?
When I try to use the 'Add To' box in the Field List Box, the option is grey'd out so I cant click it.
I'm using version 2002.
Can anyone help?
Claudia
View 4 Replies
View Related
Nov 6, 2012
I would like to be able to select a value from a drop-down and by doing so be able to populate three fields in a table.
View 12 Replies
View Related
