access-vba

MS Access 2003 - Customized Auto-Number IDs for tables?

Is there a way to use my own number in a table like an auto-number; that is to automatically assign the next available to a new record. We have system ID numbers for each employee that I want to tie into this database. I just want the table to auto assign the next number. Can I do this? Could this be done with a mixture of numeric and a...

MS Access 2003 - Listbox formatting control

Just some general formatting questions about return records to a list box on a form: I have a list box that returns sets of records based on SQL statement in VB. I need the SQl statement to return the ID (auto number) of each record, because if the user selects from this list box, I need the ID as a pass parameter to the next form. But ...

How to use Controls collection in Access 2003 and VBA

I'm having a heck of the time trying to figure this one out. I want to pass a Controls collection to a function but I get a type mismatch. Here's the function declaration: Public Function DoStuffToCollection(topCtlList As Controls, isLocked As Boolean) And here's how I call it: Call DoStuffToCollection(myPage.Controls, isLocked) m...

How to requery a subform from another form?

I've struggling with this problem on my own, then with some help, then search about it; but I haven't had any luck. So I decided to ask. I have two forms in Access 2007 lets call them MainForm and EntryForm. MainForm has a subform and a button. The button opens the EntryForm in Add Mode. What I want to do is when the EntryForm saves the...

Close modal form in Access and access module code

Hi I've inherited a Access database that is used to import some data in SQL. The MDB opens with a form, in a modal mode: no Access menubar or buttons are visible. I can view the tables with content by using the Visual Studio 'Data Connection' tool, but I cannot see the module's code. I've looked at this question here, but the answers ...

Access VBA - print last record from SQL Query

Hey I have the following SQL Query I'm executing and I'm trying to find why it returns the error 'overflow' when running the query. Now I want to print the last record that it computes before going into overflow, is this possible using MS Access VBA? Private Sub Command0_Click() Dim sql As String Dim rs As DAO.Recordset Dim db As DA...

MS Access - Data Type Mismatch

I have the follow VBA code and it returns a 'data type mismatch in criteria expression' while executing. I cannot seem to find out why it is giving me this error. Can anybody help me? VBA: Public Function GezaagdeOmzet(ByVal TotaalPrijs As Double, ByVal AantalArtiklesPerOrder As Double, ByVal TotaalArtiklesPerOrder As Double, ByVal ...

How do I create a custom property with VBA in Access

Hello I am looking for a way to create a custom property in access with VBA. Here's how far I am and where I am stuck: A custom property's value (whose name is foo) can be read like so: Dim cnt As Container Dim doc As Document Set cnt = DBEngine(0)(0).Containers!Databases Set doc = cnt.Documents!userDefined doc.Properties.Refresh D...

MS ACCESS 2003 - Question about passing information from an unbound form to a report

I use something like below to pass information from one unbound form to another (excuse the no error checking): Sub Button_Click() Dim db as DAO.database Dim rs as DAO.recordset Dim sql as string docmd.openform "NextFormIamAbouttoUse" sql = "SELECT * FROM tblMain WHERE MainID = " & Me.As_MainID & ";" ' usually debug here set db = cu...

Excel pivot refresh, save and close in Access VBA code

I am using the following code to refresh Excel pivot tables from an Access application. What is the best way to save and close the Excel app after the pivots refresh? In my last attempt the code was trying to save and close before the pivots had refreshed. Private Sub Command161_Click() Dim objXL As Object, x On Error Resume N...

how to get all tables by editing dsn file "data source file"

Dear All, By having the next dsn file: [ODBC] DBQ=C:\\SLA.mdb C:\\SLA.mdb DefaultDir=C:\ Driver={Driver do Microsoft Access (*.mdb)} DriverId = 25 FIL=MS Access MaxBufferSize = 2048 MaxScanRows = 8 PageTimeout = 5 SafeTransactions = 0 Threads = 3 UserCommitSync = Yes [Microsoft Office] DefaultTable =table1 how could I edit it to have ...

MS Access : Determine Object Type

Is there a way to determine the Object type, when passing a reference to a function? I'm using a security permissions function, which determines if the user has permission to view/edit the Form passed to it by reference. I'd like to expand this to include reports as well. To keep the function generic, I'd like to pass a ref for either ...

How can I Inner Join two tables in Access when one column is a text and the other is number?

I have a program in access that is using some linked ODBC tables. I had originally had a query that contained the following INNER JOIN: FROM Neptune_FN_Analysis INNER JOIN Neptune_prem ON Neptune_FN_Analysis.PremID = Neptune_prem.premice_id This worked just fine until the column Neptune_prem.premice_id got changed from a number to a...

How do I access other fields in the current row in a query from a custom VBA function?

I would like to write some custom VBA functions to perform calculations on particular fields in a set of queries. This is what I currently have: Public Function TVM(LEN_SEC As Double, ADT_CUR As Integer, TRK_PCT As Integer) TVM = LEN_SEC * ADT_CUR * TRK_PCT / 100# End Function This works fine, but when I create the query I have to...

Data: PHP form => email => Access 2007 database

I have a form which is submitted to a PHP page. The data from the form needs to be added to an Access database, however the website is hosted and the Access database is on a network drive. So for now, the form just emails the data to someone who enters it by hand. We're trying to improve this process. So there is no way for the PHP page...

Opening form in design view on close

I would like to make changes to a form as it is closed. I know I need it in design for to do this, but Access will not allow the form to be in design view "on unload" or "on close." Is there a work around? ...

Loading Bloomberg Api Data Using VBA Access Program

Hi Friends, we have subscription to Bloomberg data, we use excel to load data from bloomberg using bdp function. but its pain,so we decided to find easy way to do this . Is there any way to program and calculate Fixed Income Chars from a list of securities using VBA ,access,sql or anything.? Thanks ...

Microsoft Access VBA to edit SQL queries

Hi guys, I have an Access database with a single query in it. I can currently copy the query via VBA, using DoCmd.CopyObject . However, I need to be able to edit the SQL in each instance of the query individually. All the examples I have seen however, involve recordset, which is something I am not using. Any ideas? With thanks, Will ...

How to call VBA-function from inside sql-query?

Here is my query: PARAMETERS ... TRANSFORM ... SELECT ... ... PIVOT Mytable.type In ("Other","Info"); This is a cross query and I need to set all the column headings with this row: PIVOT Mytable.type In ("Other","Info") and now I have hard-coded the headings, Other and Info. But I want to do this dynamically. So what I want to do is ...

Microsoft Access - Override Maximum Font Size?

Have a Access 2007 database which needs to have a report that prints out some really big numbers (for some signage). Currently in Excel the template uses a 400 point font size. When trying to change the font size in Access the following error message appears: The setting of the FontSize property must be from 1 through 127 I tried...