Access Lookup Data Macro

Nov 16, 2011

I am trying replicate the northwind web database macro called getproductname getproductlist which is on the products table. this macro has a simple lookup action which looks up a record by a parameter being sent.

the customer order form has a productid combo box. on its after update it finds the product list. this UI macro just passess the product id to the data macro on the table and looks up the price for that id. then sets the value on the form. simple right? however i consistently get an error from the data macro on the table which says the "the identifier (enter field name here) could not be found"

in my case im selecting id and productname from my table. the syntax is correct. but when i run the macro i get "the identifier productname could not be found. ive been struggling with this for hours. and im using a working sample as an example.

View Replies


ADVERTISEMENT

Access Massive Data Lookup

Sep 16, 2014

I have created a database with a list of product codes, pricing, costs, and supplier name. Now I have about 100 product codes which I would like to look up for all those information. Is there an effective way to do it in Access? I know I could export the whole datasheet to Excel to do a vLookup, but it may be lots of waiting time if the data are massive.

View 2 Replies View Related

General :: Lookup Data Type In Access 2013 Web App?

Jan 2, 2014

I want to create a Web App in Access 2013 that contains a table of client names, addresses etc, and a second table that contains order details, including client name. It would be nice to ensure that as someone adds a new order they are give a drop down menu containing existing client names, and I can see how you can do this for a brand new table using the lookup data type. But I already have an Excel spread sheet containing client names. If I import this into my Access web app to create the client table, and import the existing orders to create the order table, I then try and change the data type of the client name (in the order table) from short text to lookup, it won't let me!! (If I create an empty client table from scratch, it lets me define the company name as a lookup data type - but I can't then import from Excel into this empty table)

View 14 Replies View Related

Tables :: Lookup Field Using Another Multi Value Lookup Field As Data Source

Nov 23, 2012

how to do a particular thing in Access 2010 (I don't even know if it is possible).

I have a table named PRODUCTS:
ID_PRODUCT (primary key, autonumber long integer)
ALLOWED_OPTIONS (multi value text lookup field: "Option 1";"Option 2";...;"Option 9")

So I can store, for each different product, none, one, or more options to let the customers choose from.

I have a table named ORDERS:
ID_ORDER (primary key, autonumber long integer)
FK_CUSTOMER (foreign key, linked to the primary key of a CUSTOMERS table; represents the customer that places the order.)
FK_PRODUCT (foreign key, linked to PRODUCTS.ID_PRODUCT; represents the product that the customer has choosen)
CHOOSEN_OPTION (lookup text field; the customer must choose ONE option among those allowed for the product he has ordered)

The problem is that I would like the CHOOSEN_OPTION field to show as a combobox, listing the values stored into PRODUCTS.ALLOWED_OPTIONS, so that when a customer buys a product, he can choose only among the options allowed by that particular product.How can I manage a multi value field to populate a combobox, in which every item stays on its line? If I use, as a query to populate the combobox:

select [PRODUCTS].[ALLOWED_OPTIONS]
from PRODUCTS
where [PRODUCTS].[ID_PRODUCT]=[FK_PRODUCT]

I obtain an empty combobox.If I refer to the last field as [ORDERS].[FK_PRODUCT], Access asks me to type a value for "[ORDERS].[FK_PRODUCT]", treating it as an unknown parameter.I think that the problem is that when the combobox expands, the record is not committed yet, so FK_PRODUCT is unknown (NULL?). But this happens even if I commit the record typing something in FK_PRODUCT and then I re-enter the record and I expand the CHOOSEN_OPTION combobox, that is still empy although FK_PRODUCT exists, now.Is there a particular syntax to refer to a field in a record not committed yet (something like "THIS." or "ME.")?

View 5 Replies View Related

How To Run Data Macro Using VBA Code

Jun 18, 2015

I try to run Data macro using VBA Code.

Code:
Private Sub cmdRunDataMacro_Click()
DoCmd.SetParameter "prmMetalID", Me.cmbMetal
DoCmd.RunDataMacro "Metal.GetMetalParameter"
DoCmd.SetProperty txtTestMetal, acPropertyValue, [ReturnVars]![retPrice]
End Sub

View 2 Replies View Related

Using A Macro To Input Data In A Field

Jul 17, 2006

I have a button that will do two different functions 1 to open a form and 2 input data into the classType field. In order to do this would I need to use a Macro with setValue? How do I do this?? When the button is clicked input “CPP” in to the classType field of the EqTable.


Please Help!!
Jessie

View 1 Replies View Related

Stop Macro So I Can Input Data

Mar 27, 2006

I have a form that sets criteria in a series of make-table queries that go on to power a report. I've set up a macro to first open the form and then run the make-table queries and then open the final report. My trouble is that before I can set the criteria in the form I'm getting prompted by the make-table queries.

How can I stop the macro so I can input data and then re-start it? I tried using a stop macro command but that didn't work out for me.

Thanks

View 5 Replies View Related

Expression Builder In Data Macro

Nov 13, 2012

I'm facing an issue when I'm creating a Data Macro After Insert in Access 2010. In the value for the SetField, I use the expression builder. I wanted to use the DateAdd function but it not showing, also I cannot see my user define function (Module).

This behaviour seem to occur only in the instance of Expression Builder while in the Data Macro, everything is fine anywhere else including macro.

View 5 Replies View Related

Modules & VBA :: Write A More Complex Macro That Will Start Another Macro At Preset Time

Dec 8, 2013

I am trying to write a more complex macro that will start another macro at a preset time, however I am getting stopped at the first hurdle - getting a macro to run another macro.

Here is the code i am using at the moment, all I want to do currently is click the first button, then get the second macro to execute. But no luck, getting error 2157 "cannot find the procedure"

Code:
Private Sub Command3_Click()
MsgBox "1st macro running", vbExclamation, "Note"
Application.Run "teststart1"
' Application.OnTime TimeValue("19:55:00"), "teststart1"

[code]....

View 1 Replies View Related

Tables :: Data Macro For Audit Trail

Sep 18, 2014

I am having a bit of trouble with one part of it. The DB I am putting together has a login form with a combo box for the user name, and a textbox for password entry. A small macro tied to a button compares the password input to a column in the combo box that pulls from a User table. If the password matches, a main nav form opens, and the login form goes to hidden. What I am trying to do is grab the User Name from the combo box, and store it in a field in the audit trail table when a user is in one of the data entry forms.

View 2 Replies View Related

General :: Macro To Manipulate Unstructured Data

May 5, 2015

I have some tables need to be imported to Access, which is not well structured. For example:

report
4/05/2014
from SQL server
Name ID Add.....
aaa 111
bbb 222

To be more specific,I have 4 csv files, that need to be imported into Access.

1. I'm building 4 linked tables so users can update the table as needed. Then run queries based on these tables to produce report. But maybe there's a better way? the file name and location might be dymatic, so anything like 'getopenfile' would be easier instead of linked tables?

2. These data need to be clean up first to become a 'database table'. (Delete first 2 rows, delete some columns, remove duplicates etc.) I know how to do them in VBA Excel, but never used Macro in Access before.

View 1 Replies View Related

Macro Transfer Data For One Table To Another By Columns?

Nov 2, 2011

I have one table with the following records:

Name Surname School Date1 Date2 Date3

John Smith London 12/12/11 11/11/11 10/11/11
Mary Wayne Harvard 11/10/09 12/10/13 10/11/11

I would like to design a Macro which can create another table like this :

Name Surname School Date
John Smith London 12/12/11
John Smith London 11/11/11
John Smith London 10/11/11
Mary WAyne Harvard 11/10/09
Mary Wayne Harvard 12/10/13
Mary Wayne Harvard 10/11/11

That is duplicate the records, and create another table, with all the records and each record with just 1 date.

View 2 Replies View Related

Macro In Access

Dec 3, 2007

Hi,

Task: to extract data from table 1 of a database (tied to form 1) to create a record in another table 2 of the same database (tied to form 2).
Besides, I need to make it simple to use for an end user.
On the form 2 I have a command button that activates macro. Macro makes a query to run and extract data from table 1 and append a table 2.
Now I want the user to see the record on the screen (form 2) that has just been created. For that purpose I add "go to last record" step in the macro.

Problem: that doesn't work. :) For some reason it brings back same record from the middle of the table which is not the last. And what is even more interesting is that it doesn't tie to the record ID on the bottom of the screen (access generated).

Challenge: the record ID field in the table 2 is a primary key auto-numbered field (i know this is not perfect but I am not the one who created the database) and some records have been deleted over time. Might that be connected to that or that is something else?
Thank you!!

View 1 Replies View Related

Modules & VBA :: Stop Macro Error If There Is No Data To Find?

Jul 16, 2015

I completed the Op[en form and find specific data wizard and it works.

Here is my issue: If there is no data to find, I get a "Stop Macro" error.

How to change this?

View 1 Replies View Related

MS Access - Project Macro

Oct 7, 2005

I have recently made a database and through MS Development Tools have been able to bundle it with Access Runtime. We are using MSDE as a backend and when I install the 'package' created by MS Project on another computer it says that it cannot find the SQL Server database.

This will mean (i believe) I will have to write a macro to reference the external SQL database (MSDE)?

If anyone has knowledge of this or/ and can point me in the right direction it would be appreciated.

Phil

View 3 Replies View Related

Access Cant Find The Macro 'Me.'

Mar 28, 2006

Hello

I am using the code below to populate three fields by selecting the data from one combo box

In the AfterUpdate event of a cmbBox I have entered the code:

Me.[Part Code] = Me.cmbBox.Column(0)
Me.Description = Me.cmbBox.Column(1)
Me.Cost = Me.cmbBox.Column(2)

When I then try to select from the drop down list I get a message sying that the Macro cannot be found. What am I missing?

View 1 Replies View Related

MS Access Macro Problem

Jan 5, 2006

I have a MS Access database which requires that a MS Word document be opened via a macro.

In the Macro design view under the Action tab I have the Macro set to RunApp. Then in the Command Line I have:-

C:Program FilesMicrosoft OfficeOFFICE11WINWORD.EXE C:Documents and SettingsKnightALDesktopDataBase AssignmentLetterhead.doc

When the macro is run I get an error. It begins to open the Letterhead.doc document. Word begins to open but then a messages saying Word experienced an error trying to open the file. With as few suggestions on what to try none of which make any difference.

If I change the Command Line to read:-
C:Program FilesMicrosoft OfficeOFFICE11WINWORD.EXE
Then all is well and a blank Word document opens.

I have tried quite a number of variants in the command line but all give me no joy at all. So can any one tell me please what it is I am doing wrong here. I can even get the macro to open my Spyware program but cannot get a Word document to do its thing.
I have put copies of the document in all sorts of places but still it cannot be detected.

View 7 Replies View Related

Run Excel Macro From MS Access

Sep 11, 2006

man am i stuck.......here goes. i am using a mdb that i upload a weekly excel file to build my weekly report.

the weekly excel file is called Subledger Current.xls

before i upload the excel file i run a macro that is stored in an a different file called Converter.xls on my c drive (C:Converter.xls). the macro name is "Converter_Macro". also, the workbook is hidden, therefore, when i open this ms excel file i have to go to window and unhide to view the workbook. after that, i have verified that the macro is part of the list under Tool Macro Macros.

With that said, can anyone identify my error in my code why the macro is not running. that is, the code does not error out but the macro is not running.

thanks
terry

Code:Private Sub cmdImport_Click()Dim appExcel As Excel.ApplicationSet appExcel = New Excel.ApplicationExcel.Application.Workbooks.Open "C:Subledger Current.xls"Excel.Application.Visible = False'this is where i call another sub to open the file containing the macro and attempt to run itCall Sub xlAddin()'i have addition code here to finish out the prodecure which is function properly when i step through manually.End SubSub xlAddin() Dim objExcel As Excel.Application Set objExcel = CreateObject("Excel.Application") objExcel.Workbooks.Open "C:Converter.xls" ' Runs the macro objExcel.Workbooks("Converter.xls").RunAutoMacros (xlAutoOpen) objExcel.Application.Run (Converter_Macro) objExcel.Quit Set objExcel = NothingEnd Sub

View 2 Replies View Related

Access Macro - ECHO Yes / No ???

Oct 20, 2006

As I can't seem to find another way to make Access add together two seperate columns of numbers (i.e in Excell it would be A2+B2 = ) I've created a macro of some 42 lines long that copies information from a form into a table, from where it's summed in a query and the results shown on another form. The macro "Steps" through the process without a problem and will rune through the process with "ECHO - YES" selected. However, if I select "ECHO - NO" the screen freezes (although from the "Bong" it appears the macro has finished running) with the tool bars at the top missing and only the initial form infront of the Database window, on display. I've left the computer (actually a couple of computers - a laptop, a high spec desk top and the works' networked computer) for up to 15 mins without anything changing. Once (I think) I was able to shut down the database using the normal windows red / white X button in the top corner of the programme screen, otherwise I've had to Ctl / Alt / Del to end the programme. The other strange thing seems to be that I can open and work other programmes while the Access screen is frozen so it looks as if the computer isn't freezing (The task manager CPU useage bar is only up to 15 - 30 % as well)
Any ideas what may be going wrong or how I can get round this? I'd perfer to be able to run the programme in the "ECHO - NO" state as one of the guys I'm building it for is blind and has his computer talking to him. It makes a hell of a racket as the macro runs in "ECHO - YES"!!!!!!

View 1 Replies View Related

How To Run Access Macro In Certain Times

Mar 19, 2013

I have my data updated 3 certain times in a day. So i dont want to bother opening the same access 3 times a day. That access can be opened whole day no problem for me. So, how to run macros automatically in a time which I determine before ?

View 6 Replies View Related

Access Macro / VBA To Load Up A PDF

Jan 23, 2015

I have 1900+ records, of where each record is represents a PDF Document in my folders.Each document in my folders is named in the same format "2014-001a" for example. This is the combination of two fields in my Access Database - Document Number and Current Revision.

I am looking for some Macro/VBA where I can put the same command on every records form, that will then recognise the Document Number and Current Revision of that record, and then "On Click" able to directly load up that Pdf.

View 1 Replies View Related

Reports :: Macro That Opens Exports The Selected Data To A Form?

Feb 23, 2015

I currently require a macro that takes the record and when clicked, it opens a form and displays this record. This is so that I can use it to click buttons and open existing reports based on the data and field that match the 'clicked' record.

View 1 Replies View Related

Modules & VBA :: Password Parameter For Import / Export Data Macro

Jul 16, 2014

I am creating a ImportExportData macro that will link to my tables in the BE

However the BE has a password. I was told this would require coding.

I have even tried to 'convert macro to VB' and i get this:

Code:
DoCmd.TransferDatabase acLink, "Microsoft Access", "c: est.accdb", acTable, "Table1", "Table1", False

I then tried to enter the password in the last segment (StoreLogin) ie:

Code:
DoCmd.TransferDatabase acLink, "Microsoft Access", "c: est.accdb", acTable, "Table1", "Table1", False, "PASSWORD"

but that didn't work either, i got a "An expresiions you entered is the wrong data type for one of the arguments"...

View 2 Replies View Related

Lookup Data In Another Query

Sep 14, 2006

Hi Folks
I have a form that is based on a query.
I want to change the unique ID that is displayed on this form.
The new ID is sourced from a different query. I.E. Not the same query that the form derives its information.

I have set the data source property of the field (Combo1) to the "other" query.

How do I get the Combo1 to update to the value of the first record?
Currently its defaulting to Null.

I have tried using :
Me.Combo1.Requery - This does NOTHING to the vaue.
Me.Combo1.MoveNext - I keep getting a "Method or data members not found" error.

View 7 Replies View Related

Lookup And Update Data????HELP

Feb 24, 2007

i want to update data fields (time fields) in a table based on a number input by user (so it will search for record in table using the number and then update the relevant fields)....How can i do this am quiete confused there PLEASE DO HELP

View 1 Replies View Related

RunApp Macro In Access 2007

Jun 22, 2007

In access 2003 their is a RunApp macro to run applications from a macro. In access 2007 (at least my copy) it does not show up. Is it my copy or has it been taken out of this version.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved