Adding Function To Database
Mar 1, 2006
Any help would be really appreciated...
I have a database in the database I have products customers orders etc.
In Products table I have productid price etc. I would like to create another table and this table should consist of all product parts for example.
ProductID x uses
metalnumber23
stone45
plasticnumber43
so all metalnumber23, stone45, plasticnumber43 will be in Parts (new table that i will create) when i am entering a productid to Product table for that productid i should be able to choose which parts from parts table that it uses.
so in the order table if i customer ordered productID x I should be able to see all the parts that ProductID needs in the parts Parts table. There will be maybe 2000 parts but ProductID x will use only 3 of them. And if I create a query lets say order from z date to y dates I should be able to see parts needed for those products ordered.
hope you guys understand and give me a head start i would really appreciete
View Replies
ADVERTISEMENT
Mar 15, 2006
Hello,
I'm trying to sum values in a table and the result I'm getting is incorrect.
I have a simple table called Test with one row called Number which is Double. I have 4 values in Number field. They are:
-60000
26.6
2661
62849
When I run a query to sum those fields:
SELECT Sum(Number) As Sum FROM Test;
I get the following result:
5536.60000000001
Interestingly if I change the last number in my table from 62849 to 62848 I get this result:
5535.6
The problem seems to be with summing positive and negative numbers together. In my case, if the sum of positive numbers goes over 65535, the resulting sum is incorrect (adding 0.00000000001 to the result).
Does anybody know why this is happening and if there is a way around this? The actual tables that I work with have a large numbers that go well over 65K so this is a problem for me. I couldn't find anything on google about this.
View 2 Replies
View Related
Mar 8, 2008
Hi Guys, sorry to bother you but I'm really stumped. I've looked through some similar sounding old posts on here but they don't seem to answer my problem...
I've got a report, which is grouped, in the footer of each group a SUM label should add the contents of the pricce column, however strangely it only adds the first record of each group as a running total. I've tried both the "Over all" and "Over group" attributes of the running sum property, but they make no difference.
Any help would really save my day. Cheers!
View 11 Replies
View Related
Nov 22, 2006
What is the best way to implement a search function into a switchboard? I need this search function to allow a user to check a database for specific names, so that they do not enter duplicates. Thanks in advance for all advice
View 7 Replies
View Related
Nov 24, 2006
Hi guys and girls,
I'm fairly new to Access and I'd appreciate some help. I've built a form with buttons to open new forms for that specific record (based on queries as all the data is held in a single table). This works fine until I try to add new records. At the moment the only way I can make this work is to add the new record, shut the original form before re-opening it and proceeding on to the other sections (via the buttons I mentioned).
If anyone could tell me how to get Access to save the record before it opens the other forms I'd really appreciate it.
Cheers,
Franny.
View 2 Replies
View Related
Sep 13, 2014
In my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
Code:
Me.Date_and_Time_Complete_Package_DUE = DateAdd("h", [SLA_Time], [Date_Complete_Package_Received])
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
Code:
Me.Date_and_Time_Complete_Package_DUE = WorkHours(DateAdd("h", [SLA_Time], [Date_Complete_Package_Received]))
I have a Workhours module and it is used for a lot of things and it works perfectly.
View 1 Replies
View Related
Nov 12, 2013
I have a command button that basically saves the record that i just finished entering. here's the code:
Private Sub cmdAddAnother_Click()
On Error GoTo Err_cmdAddAnother_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit_cmdAddAnother_Click:
[Code] ....
What I'd like to do now is add a second function that will make all the controls on the form go to null after the record is saved. so the user can start from scratch and add another record.
I tried adding me.refresh right before "end sub" but that didnt work.
I tried adding "me.controlname.value = null" for every control on the form, and that didnt work either.
I also tried adding this code that i found on bytes.com:
On Error Resume Next
Dim ctl As Control
For Each ctl In Me.Controls
ctl.Value = Null
Next
That didn't work either.
View 4 Replies
View Related
Oct 31, 2007
Hi Folks,
I have created a file sweeping program in Microsoft Access via VBA in a few modules. The program simply reads certain files in certain directories, and determines if, when, and where to copy those files based on certain user inputs.
One of the features I would like to add would be to upload the files that meet that criteria to a conventional FTP server requiring a username and password. However, the last few days of research all tells me that it is not possible to perform FTP functions in Microsoft Access or in VBA.
I am just wondering if that statement is really true? I figure the worse case is that windows has its own version of FTP.exe that you can execute in a DOS window ... would one be able to perhaps create a command line execution via VBA? Any simple ideas or is it not worth the trouble trying to FTP upload some files via Microsoft Access with VBA?
Thank you very much,
Joe
View 1 Replies
View Related
Aug 7, 2014
I have 2 database files.
database A has a button (buttonRun) that when clicked, it opens database B and runs the autoexec macro.
In database A, there's a label control (lblRun) next to the buttonRun that has its back style property set to transparent.
What I'm trying to accomplish is this:
After the procedure in database B completes, it exits the system. I want
lblRun's back style property to be set to normal.
I already have a function that does this and the function works fine from within the database A.
Code:
Dim frm As Form
Dim color As Variant
Set frm = Form_frmReportingDashboard
frm!lbl_cmslite.BackStyle = 1
I need to trigger that function from database B before it exits.
View 8 Replies
View Related
Aug 29, 2005
Hey, its been a while since I've posted here, wow.
I had a database that contained samples on different date functions
(first of the month, last of the month, first weekday, etc...)
I can not find it, I have tried searching. I am pretty sure I got it from here.
Does anyone know where this is?
View 3 Replies
View Related
Aug 13, 2013
I've been trying to create a search function in my database and a " TmprryQryFrLnkFldsCrtn0123456789 " Query has appeared that I cant alter or change.
View 6 Replies
View Related
Jun 12, 2006
Hi there, this is my first post, hopefully not too many more problems i will encounter whilst designing my database.Anyway I am trying to add some fotos to each of my records, but not having as much luck as i would like. In the tables, i have made the field OLE object. When i try to add the foto (about 97kb) jpg format, all it does is add a picture of the jpg icon on my field. If i double click on the icon my foto will open, but i wish to have the foto showing in my record. Anyone help me with any ideas? I am using access 2003
View 2 Replies
View Related
Jun 6, 2007
Hi there,
I have a bit of a dilemma here.
I have an old database, which is very comprehensive, filled with client names, addresses, and other information.
I have been given a new database, which is more relational and effectient, which splits the client addresses into a seperate table.
I need to add the old data to the new database - so is there a way of doing this whilst maintaining the links between the old addresses and the rest of the old data? What I don't want to have to do is end up going through 3000+ entries sorting out what new CompanyID I should assign...
Any thoughts?
Cheers
Hob
View 2 Replies
View Related
Sep 6, 2007
Hello
First let me apologize if I am being stupid, quite new to Access. I am trying to create a database with some pictures in it; I want to design a form that changes the image with each record, (Its for work they want a database of our employees info with a photo of each employee) reading though the help file it seams I need to add a bound image control, I have tried 2 different ways and am still getting the same problem, the first way I tried was to create a text field with the address off the photos (there are .jpg’s) and then add a bound image control on the form and link it to the field. Problem is this just displays the name of the file not the picture (e.g. as you go though the records it shows Mark.jpg, Simon,jpg, Louise.jpg….) I then tried to add a OLE field and add the files that way, if I click on add from file when creating the object I get the same problem just displays the file name, If I click create new bitmap image it works and displays the picture, the problem is the data base file then becomes huge (it was 45 meg with 2 photos, I need to add about 150 in total!. Am I being totaly dumb, is there a way of getting Access to display Jpg’s and ideally have them linked to instead of embedded to keep the size of the database down.
Sorry for rambling! (oh yeh and I am using Access 2003)
Thanks in advance!
Matt
View 2 Replies
View Related
Aug 24, 2006
Hi,
I am running an autocompact module in Access97 which will created a 'compacted version' of my database. I then want to rename this compacted version to the name of the original database I compacted. I cannot however find a way of doing this in code.
Basically the idea is to compact every 45 minutes or so, but what my autocompact is doing is creating a compacted version under a different name, and so compacting a copy of the database, I then want to change this compacted database name to the original name and re compact every 45 mins
Can anyone help?
View 1 Replies
View Related
Jun 15, 2014
I am trying to create a statistics function on a database. The idea is that the user will enter a start and end date and either search for all records during that date range or select a client from a combo box and only view records for that client during the selected dates.
After doing quite a bit of searching, it seems that I should be using a wildcard in the criteria however I cannot get this to work. The code I have been playing with is:
Code:
=Nz(Forms![Statistics]![ClientCombo] ,"*")
I have changed the "*" to a specific client number and if the combo box is left blank, results are shown for that client only and if a client is selected from the combo box then the selected client is shown. The only thing I cannot get it to do is show all entries if the combo box is left blank.
View 2 Replies
View Related
Oct 11, 2005
Im trying to add 2 new tables to a split and linked database... but no matter which side i add the table to (BE or FE), it wont show up in the linked table manager.. so my question is 1.) is there some trick to getting it to show up in the link manager? or 2.) is there some way to re-merge the database, add the new tables.. and then split it again?
View 3 Replies
View Related
Sep 3, 2007
Hi all, I am happy with my fax database until my goverment decided to add another 6 infront of all the phone and fax number.
How can I change all the numbers together?
Example from : 1234567 to 61234567
Thanks.
View 14 Replies
View Related
Oct 16, 2007
Hi all,
can i use a listbox control NOT IN A PROGRAMATICALY way, in a table to allow user choose one of some pre_defined values (for example 1,2,4). This field will be used as a parameter in an communication with HW, that's why its crucial not to let users choose whatever value that they want.
Thank you
View 3 Replies
View Related
May 1, 2005
I have a database with a backend on a network and the front end on each users PC.
When I add security in the form of a workgroup it only apllies to the front end and anyone can open up the tables on the backend.
How do I secure the whole database. Anyone have any advice on security.
Stephen
View 1 Replies
View Related
Aug 9, 2006
I'm trying to add a record to the members table in the access database located at c:dbmyforum.mdb. So I looked it up and did a copy and paste from an example, and edited it to match what i need. here's what I've got:
Code:<html><body><%set conn=Server.CreateObject("ADODB.Connection")conn.Provider="Microsoft.Jet.OLEDB.4.0"conn.Open "c:/db/myforum.mdb"sql="INSERT INTO Members (SN,Password,)"sql=sql & " VALUES "sql=sql & "('" & Request.Form("sn") & "',"sql=sql & "'" & Request.Form("pw") & "')"on error resume nextconn.Execute sql,recaffectedif err<>0 then Response.Write("No update permissions!")else Response.Write("<h3>" & recaffected & " record added</h3>")end ifconn.close%></body></html>
But it keeps coming back with errors. What am I doing wrong? I'm sure that the info from the form is going through, and I've checked the sql several times, although I'm not sure about the vb script cause I'm new to asp(trying to learn it now).
View 14 Replies
View Related
Jul 21, 2013
in my daily roll call report i have 2 groups..."on program" and "graduates" these 2 groups are creating in the query...as u see in the 2nd pic... the expression as followed
Expr1: IIf([Date Graduated]<Date(),Date(),[Date Graduated])
next you can see in my 3rd pic the report and the expression that gives the 2 groups there names...it is as followed
=IIf(IsNull([Date Out]),IIf(IsNull([Date Graduated]),"On Program","Graduates"),"Recent Departures")
i will clarify that i took out the names in the roll call but both groups are sorted by the date they came in going down the list..now i need to add another group "Staff Members" to my roll call.every way i have tried altering the query expression or the report expression result in a blank roll call.
View 14 Replies
View Related
Dec 8, 2011
I was tasked by my CFO yesterday to add a new element to my existing database. I will try to keep what I am trying to do simple.
Basically we do finance for multiple projects. each project has employers.
My database records these employers names, SSN, dates registered, program they are in, and a few other elements as well.
What I have been tasked to do is create a new table with the project managers in that table. this way in a query or report i can pull "sally sue's" projects or whoever I needed to. There are many PM's who have more than one project.
Here is what I have for relationships:
On the Project Managers table I have a PK for ID and then the PM Name as a second field. the ID field is linked to the Programs table which has a PM ID and Program name
The Program table is also related to the employer table which has all of the employers and their info. the relation between the two tables is the Program Name.
So when I run a query or report my goal is to be able to pick any project manager and get the associated count of employers under all of that projects managers programs. I have gotten errors when running queries. Perhaps I have the relationships set up wrong?
View 1 Replies
View Related
Aug 19, 2004
I am using a Access database with a frontend/backend design. I want to keep a table in the BE updated with the current list of users inside of the frontend. To do this I thought that I would just have the front end update a time field saying that that user is still inside of the FE. My question is two-fold - firstly, how can I run a function every minute or so to update this table.
Secondly - is there a better way to do this?
Thanks in advance for any help you can offer.
View 3 Replies
View Related
Apr 23, 2013
I have a database, i need to automate mail merge function from access DB to insert selected records into the merge fields, i have tried everything i could and i have searched through out the web nothing really works for me.
View 1 Replies
View Related
Jan 30, 2006
Hi
I was wondering if any one could help me.
I have a database at work with 5500 records, how ever the first 1000 have been lost. There remains records for each customer but the first 1000 records showing what they ordered have disapeared.
I have back ups of the database going back with the first 1000 records complete.
How would i take the 1000 records and add this to the current database where the information is missing? so it wouldnt mess up recent records?
could some one provide me with a step by step account. Would be much appreciated!
Many thanks
View 4 Replies
View Related
