Scenario-help (posted In Macros Too)
Aug 10, 2006
Hope I am posting my question in the correct place. First of all I am entry level user in access, but knowledgeable in excel. As I am working with data containing over 65,000 rows I decided to use access however I don’t know how create a function that will work give the same result as (vlookup) in excel. Scenario case: first column “Serial Number” contain a list of serial numbers, 2nd column will be fed manually or by a bar-scan, the third column should show the value of “1” if the value in the 2nd column exists in the first column else a value of “0” should appear
The validation formula in excel looks like: =IF(ISERROR(VLOOKUP(B2,$A$2:$A$65000,1,FALSE))=TRU E,0,1)
Serial Number Confirmed Serial Validation
141614661
141614662
141614663
141614664
141614665
141614666
Thanks in advance,
Tarek
tarek_habal@yahoo.com
View Replies
ADVERTISEMENT
Jun 19, 2007
Hi all
I really could do with some assistance with this it may sound quite simple but for some reason I cant do this?? and its driving me nuts!
I have a column name [policy] and a column name [movement type]
the rule is you cant have a policy number and different movement types
you can have many of the same but not different and there are about 8 movement types
I would like to find all the policy numbers where there are two movement types using a query.
Any help would be really greatful
Thanks in advance
C
:confused:
View 14 Replies
View Related
Oct 24, 2006
Hi, all! I really need some help from you geniuses here. I've never worked with modules and my coding from objects is very limited.
I need to create a browse button that will be used to select a file to import into my database, using a command button. I hope this makes sense. I was reading the post:
http://forums.aspfree.com/microsoft-access-help-18/how-to-link-fe-to-be-using-vba-76392.html?&highlight=browse+button
I am so limited in my aptitude for coding that I was totally lost reading this. I think that it is for relinking a database, with a browse functionality. Can anyone help me figure out how to adjust this to work for my needs?
Thanks!
KellyJo
View 1 Replies
View Related
Apr 4, 2007
I have a table that has debits and credits in two different columns along with other info.
as you can see from the attached Excel there is one debit for each credit. in some cases though there is one debit and 2 or more credits that sum up that ammount.
the common thing that Debits and Credits have is the project number.. but this alone will bring me all the entries!
how can i finf these lines? i have tried all sort of queries but nothing works.. anyone can help... its one thing i didnt think of..
thanks guys in advance...
View 12 Replies
View Related
Apr 20, 2006
I know how to do a "Like '*xyz*' " type condition in a query. How do I set up a "Not like '*xyz*' " type condition in a query?
Thanks for your help. This forum is wonderful!
View 3 Replies
View Related
Jan 28, 2008
All -
I have another update query that is basically looking at all of the Enrollment ID's that are associated with a ClassID. If each EnrollmendID has a Graduation date and the Graduated check = yes, then I want to place the current date in the Closed Date field for the Class.
I can get a select Query that shows all of the classes and all of the EnrollmentID's but I'm not sure how to tell the query, "For Each ClassID, If All EnrollmentID's have a Graduated Date and Graduated = Yes, Then Class Closed Date = Date()"
View 1 Replies
View Related
Aug 29, 2014
In the test db included the report works fine if every Rep (representative or User/Admin) has records assigned. But, for example, if I go to the table and re-assign the first record to another user/admin (pick list in the table field [Rep]), then `User01’ will have no record assigned at all. Then an error message appears: The Microsoft Office Access database engine does not recognize 'User01' as a valid field name or expression. And no report is produced.
I wanted to try solving it with the info on thread searched by: "cross tab query reports", (13th result) but to be honest I really don’t know how to implement.
View 2 Replies
View Related
Oct 20, 2006
This post is really aimed at someone that has experience of network databases and the problems they can pose but if you can help at all that would be great!
I work for an insurance company and we have created a database for registering complaints on. The one we currently use is a single .mdb file which can be shared by up to 60 people at any one time. This is creating a lot of problems when the queries and some VBA code are run. I therefore made a server file which is just an mdb file with data tables in and NO forms or queries. This file is stored on a shared drive on the network server. We then created a client file which contains all of the forms and queries and code. This file contains linked tables to the server so when data is entered into the form, it "Sends" it to the server mdb file. The client is installed on each of the local machines c:. The problem with this was that if we made a change to the database, we would need to reinstall the client on every single user pc. This would of took ages. I therefore made an auto-update function that checked the version number on the server and if the server number was greater than the client number, a simple .bat fiile was run which copied the updated client file from the network to the relevant users local disk.
The problem we have now is that our IT department are concered that if we make a change to the client and all the staff log in at 9am for example, it will start doing multiple copying of a file around 8mb in size to around 60 machines. They are only running on a 2meg pipe so this could cause some problems. We are not looking to change the client & server idea but does anyone know if this will have a big impact and infact if the updates for 60 machine is the equivalent or lesser of 60 people sharing the single file i mentioned earlier. If the IT dept are happy with 60 users accessing the same file at the same time, which they currently are, why are they unhappy with it updating these machine using the new method.
Would really apopreciate any thoughts anyone has..
Thanks a lot.
Gaz
View 6 Replies
View Related
May 22, 2013
I have a form and subform. The main form shows some customer details, and the continuous sub form shows that customer's charity donations.The code below runs when the form opens, and binds ADO recordsets to the two forms. The binding appears to be successful.
However whatever I do I can't make the subform update correctly to show the relevant customer donations. For example, when I use the **'d lines to update the link child/master fields, I get a "Data Provider Could Not Be Initialized" error.
Code:
Private Sub Form_Load()
Dim cn As New ADODB.Connection
Dim rsCust As New ADODB.Recordset
Dim rsDons As New ADODB.Recordset
[code]...
View 7 Replies
View Related
Sep 22, 2004
hI,
I have a form which has several checkboxes and several textboxes and a command button. the user selects some checkboxes inside the form. when he clicks the command button, i need to run the select query. the select query would be like "select col1,col2,col3 from tablename where check1 = (checkbox1 value) and check2 = (checkbox2 value) and check1 = (checkbox1 value) and so on. ". i have tried this query using VBA. it is working fine. the problem is i have run another query which is very long in characters(like 15 lines involving self join). when i tried to run this query inside VBA i am not able store the query inside a string. Is there a way to come around this problem? I think it would be easy if i can use macros to build this query. please suggest me the ways to do this.
Thanks,
Sridhar!!
View 1 Replies
View Related
Mar 11, 2005
I inherited this database where there is very little code, most of the commands are in macros. I know nothing about macros. I am having a problem with the database. Some of the records are not printing when I print the schedule(this is a scheduling database with dates and job quantities). There is a display checkbox that gets checked when we want a job printed on the schedule. Sometimes it will not print it if we do not have a date for when the files are due or when material is due, so if we put those dates in they will show up on the schedule. Lately though I can't even get it to print some of the jobs if I put in the date when the files and materials are due and when they come in. They just don't want to show up. If anyone could help, I would greatly appreciate it!! Thank you
learnasugo
PS I am working on getting db small enough to paste. Don't know how else to make it so you can see db.
View 9 Replies
View Related
Jul 26, 2005
I am trying to invoke a microsoft Word recorded macro ("Macro1") in the microsoft Access VB code. I am trying to do this after I open the Word application and the file. This is my code so far:
Dim objword As Object
Set objword = CreateObject("Word.Application") '
objword.Visible = True
objword.Documents.Open ("I:FIRST DRAW CHECKLIST.doc")
objword.Run Macro1
The bolded line is the one supposed to tell word to play the recorded macro but after it opens the document, Access gives me a message that says it can't run the macro. Can anyone tell me the correct syntax that will help my code work?
View 2 Replies
View Related
Jun 28, 2005
When you need to run a series of Queries.
And they must be sequential.
Is it better to use a Macro or VBA to call the queries?
Thanks Everyone.
Joe
View 2 Replies
View Related
Oct 4, 2004
I've got a project where an existing access db is being converted for use on a web site.
Our problem is that several of the macros apparently reference an 'S:' drive.
Is there any way I can edit these macros to change / remove the drive reference?
View 1 Replies
View Related
Aug 18, 2004
I have a report that spans 4 years, and for each year it takes about 25 queries to produce the report. The queries for each year are only different in one number in the criteria, so what I'm trying to do, instead of having to manually copy and edit all of those queries every year, is make a macro that will do it instead.
I can make it copy all the queries and rename them, but I can't make them change the criteria in the queries.
View 3 Replies
View Related
Sep 19, 2005
I'm leaving my job of 21 years soon to finish school and hopefully moving on to bigger and better things doing what I like (hopefully something with DBs) and I've got 2 DBs I've built for them. The other day I combined them into one and really started to make sure that there was no need for them to ever get into design mode (not that they could without me -thanks sbaxter).
So while I was testing all the cmd buttons, there's some reports that were made in the early stages that are fed by a parameter query, and are opened by a macro. I've found that the more I'd learned, the less I use macros now, but I did more in the beginning. Anyway, when the input box pops up looking for the parameter, when I hit cancel, I get the "Action Failed" box and then the cancelled by user box. How can I avoid them getting the first error box when they decided they don't want that option?
View 4 Replies
View Related
Jan 17, 2006
I am using Office 2003 on XP. I have a number of reports generated by macros in the database. Once I moved the application to a different pc the macros don't respond, I have set the macro security to low and still no. If I operate the application through the network all works fine but using at the pc no. Any suggestions?
View 2 Replies
View Related
Mar 1, 2006
Hi there, forst post here so go easy.
What i need to do is copy the contents of certain fields in a subform onto empty fields on the primary form, can i do this, i gather it wold use a macro, but i checked no the list and i cant see anything wihch immediately jumps out at me as doing the job, any help
Thankyou, dragon2309
View 2 Replies
View Related
Jun 5, 2006
I have built a fairly large access database (office 2003) and unfortunately I was a bit careless about naming my controls and macros. I would like to go back over all of my form controls and macros so that they have more descriptive names, but I'm afraid this will break alot of dependencies. How can I have the macros autoupdate to compensate for renamed controls and vice versa?
View 2 Replies
View Related
Aug 23, 2006
Hi,
I am using a macro to export a table to an excel spreadsheet. It seems like I have to write the absolute path in the macro's file name property, otherwise it doesn't work. The problem with that is that I have to change the path name whenever I move the folder to another location.
Any help will be very much appreciated.
B
View 3 Replies
View Related
Mar 7, 2007
Just started with 2007. For some reason I can not import any of my macros from 2003. I get a message "You don't have permission to import, bla, bla,
In the same message it also say:
"you must have read design and read data permissions".
It sounds like that I don't have full rights, although Im the admin of the DB.
Now I can create new macros in 2007 but just can't import any.
Any ideas.
View 4 Replies
View Related
Jul 17, 2007
Hello everyone! I’m new at building databases; actually, I’m learning as I go as I’ve never had any formal training and this is my first significant attempt. So with that said, I’m hoping someone out there may be able to help me and I apologize in advance for how long this is. A little back round—I’m writing a database to create a report showing the loan history on an account—basically it shows how many loan payments came in with the dates they were processed and amounts of each payment. I created a table that is supposed to populate after entering the policy number, original loan amount and original loan date into a form. I have a macro that when a button is pushed on the form, should run a query (this query is made up of the table I created as well as tables from our data warehouse and when ran, gets the loan payments, dates processed, amounts, etc), opens a report in print preview (showing this loan info), and then deletes the record from the table that I created via a delete query (I do not want a history of each policy number that is entered into the form). Now to the issues—when I enter the info into the form, it does not automatically populate the table that I created, therefore the report does not show any loan data and obviously the delete query would not work. If I move to the next blank record, the table does populate with the info I just entered in the previous record, the report runs, but the delete query still does not work. I’m not understanding why my table won’t populate right away—the record source on the form is my table. I assume the delete query not working could have something to do with my macro? Here is how I have my macro set up:
Action: Echo Action Arguments: (Echo On) No (Status Bar Text) blank
Action: SelectObject Action Arguments: (Object Type) Query (Object Name) qryFormInfo (In Database Window) Yes
Action: OpenReport Action Arguments: (Report Name) rptFormInfo (View) Print Preview (Filter Name) blank (Where Condition) blank (Window Mode) Normal
Action: SelectObject Action Arguments: (Object Type) Query (Object Name) qryDeleteRecord (In Database Window) Yes
Action: Echo Action Arguments: (Echo On) Yes (Status Bar Text) blank
So if any of you made it this far, can anyone see anything obvious that I have wrong? Just a warning—I’m not good with VB code. I appreciate any input/suggestions you may have.
Thanks for your help!
View 1 Replies
View Related
Dec 8, 2005
hi guys!
Is there a way to run a query with macros? I don't want to open it. I just want to run it, generate a report which will then be sent via email. How do I go about it?
Thx a mill!
cheers:D
View 1 Replies
View Related
Sep 23, 2004
I want to run a existing query residing in my Access database using macros (MS Access 97)
Can someone suggest me the steps to go ahead ?
View 11 Replies
View Related
Feb 11, 2005
Hello all,
Ok, let's see if i can explain this so that anyone trying help can understand. I have a form that's unbound (Music) with a subform (Arist Info)that is bound. The subform also contains two other Subforms (bound). The subform (Artist Info) contains a few Macros set up to do
some general searches with some Option groups and also one cmb box with vb code that access wizard wrote up. They all work great, with no problems at all when I open up the subform all by itself. It's when Main form is open where i run into some glitches. In then option groups all tgl & radio buttons work with exception to the two named ALL. I get a ballon message stating There is no field named
'GenreGroup or AlphaGroup' in the current record. Genre and AlphaGroup are the names of the option groups. The cmb box dosen't even work at all. There ars some other problems also but i'll ask for help later if i can reslove this. LOL I've attached a bare bone sample db to look at. I thought I was doing great learning new things every day, till I ran into this one.
Thanks for your help
Rich M.
View 4 Replies
View Related
Apr 7, 2005
Hi guys,
I’m working on a report that I want to show on the web as a Snapshot file. I created a Macro using OutputTo method to export the report. If I type the file name and location on the Output File it works perfectly, but the problem is that I want Macros to read the file name from a combo box since the file name will change every day. Here is the code I got so far.
C:Test””&FORMS!frm_FORM2!cbo_Name&”.snp”
Every time I run this Macro I got the following message:
“The report snapshot was not created because you don’t have enough free disk space for temporary work files.”
Now!, when I type the file name to that specific location Macro does the job perfectly.
Is there any way I can get the File name from a combo box located in FORM2.
Thank you so very much in advanced. Your help is always a blessing.
Mosquetero
View 2 Replies
View Related
