Difficulties With System

Emailing Difficulties
I've been working on a database for the last month or so. It's been a slow process since I've been learning Access and VBA in the process.

But my supervisor wanted a copy of it as a progress check to send to his boss. So I sent an email with a copy of the database as an attachment.

THe email went through, but when my supervisor tried to open said email, a dialogue came up saying that Access couldn't open the file because it was "out of the intranet or on an insecure site" or something along those lines

I was just wondering what this meant and how I would be able to send my boss a copy of the database so that it can be reviewed and such. Would this require splitting it?

Thanks in advance,
Nathan

View Replies !

---

Lookup Difficulties
I'm having trouble creating my query
I have a table representing the company's products. Ever once in a while the products get replaced by a newer product. Like below
_____________________________
Product - Replaces - Replaced By
a..................................b
--------|----------|-----------
b.................a...............e
--------|----------|------------
c
--------|----------|------------
d
------------------------------
e.................b

Can someone help me write a query so the result below will be shown in
three fields.

Product - Replaced by level 1 - Replaced by Level 2-Replaced by level 3
....a..................b.......................... . e......................and so on

View Replies !

---

Difficulties On My Form. Help Please!!!!
Hi! I have a problem with my form [EDITAR], the problem is that i did a form basing me on a table, but now i want to create a command that when i press it, it has to let me edit my data. i don't know how to explain me very well. i want to change my data but changing it with my form and save that information.

the other problem is that i want to create the same form for but only to add new data on the same table, i want to create a command that i will press if i want to add new records.... help i really need help:confused:

View Replies !

---

Update Difficulties!!!!!
Hey guys,
This has really got in my nerves.....

I have a table with 2 fields (pr_row, pr_col).
All i want to do is:
I want to update the table so that every 21 records, the pr_col field will increase from 1 to 21 and so on, and the pr_row field
will remain with the same value (but increasing by 1 every 21 records)
Explaining
pr_row-pr_col
===========
1 - 1
1 - 2
1 - 3
.
.
.
.
1 - 21
2 - 1
2 - 2
2 - 3
.
.
.
What is the update query that i must execute?

View Replies !

---

Difficulties With Getting The Right Responce From My Queries
Hello,

I am having difficulties with working out how to create a correct query from my database. :confused:

These are the two results I require:

What strength of whisky sells the best at Christmas (How do I do an 'In-Between' date condition regardless of the year? i.e. >= #01/12/____# AND <= #31/12____#)
Which companies have not purchased at least one bottle from each region?

However, I am not sure how I would go about getting these specific results from the database. I have done serveral previous questions based on the database (which can be seen in the .mdb file) however these two are proving troublesome. I have attached the database to this post if anyone who is at ease with queries would be kind enough to have a quick peeky at it.

Any help would be greatly appreciated !

Note. the database information is fictisious. :rolleyes:

View Replies !

---

Access Importing Difficulties
Hey,
I have an access database that imports a certain range of a certain sheet from 30 files all of which are in the same folder and I am trying to get that BOTTOM MOST peice of code to automate the process.

Now one of my many complications was that the excel files are workbook protected, and so it would give the 'cannot decrypt error' that’s why I have all that code regarding unprotecting it... but for some reason its not working

It gets to the line where it gives it the password and it tells me the password is unaccepted, and yes the password is correct...

It must have something to do with the do loop, because it works fine whenever I used it over one import file and thus without the loop... Like this for ex works perfectly fine

Code:Public Function TransferSP()ExcelFile = "G:CBTFILENAME"On Error GoTo ErrTrpStartAgain: DoCmd.TransferSpreadsheet acImport, 8, "TEST", "G:CBTFILENAME", True, "Access_Upload!C13:L34"xlapp.ActiveWorkbook.Protect (blah)ErrTrp:If Err.Number = 3161 Then 'Encripterror so unprot wb xlapp.Visible = False 'Open Excel xlapp.EnableEvents = False 'Disable Events xlapp.workbooks.Open ExcelFile 'Open File xlapp.ActiveWorkbook.Unprotect (blah) 'Unprotect xlapp.ActiveWorkbook.Save 'Save xlapp.EnableEvents = True 'Enable Events xlapp.ActiveWorkbook.Close 'Close File xlapp.Quit 'Quit Excel GoTo StartAgain 'Try and Import againElseEnd IfEnd Function

So Please tell me what is wrong with this bottom piece of code...

And secondly, I have a table which contains a list of names, whether they are active (checkbox) and their filenames each on a column of its own...

Can anyone show me how i can include into my code (assuming the password problem is fixed) that would allow me to place an if statement that checks whether the user is active or not... IF he is then it imports his file (using the filename column next to his name).... If he is not active then it just goes onto the next person without importing him/her

Right now I have it importing all the files in that folder

Code:Option Compare DatabasePublic xlapp As New Excel.ApplicationPublic Sub ImportAll() Dim strPath As String Dim strFileName As String strPath = "G:CBT" 'Set Path strFileName = Dir(strPath & "*.xls") 'Set first file Do On Error GoTo ErrTrp DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" ErrTrp: If Err.Number = 3161 Then 'Encription error so unprotect workbook xlapp.Visible = False 'Open Excel xlapp.EnableEvents = False 'Disable Events (Macro's) xlapp.workbooks.Open strPath & strFileName 'Open File xlapp.ActiveWorkbook.Unprotect (blah) 'Unprotect 'Try and Import again DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" xlapp.ActiveWorkbook.Save 'Save xlapp.EnableEvents = True 'Enable Events xlapp.ActiveWorkbook.Close 'Close File xlapp.Quit 'Quit Excel Else End If strFileName = Dir() 'look for next file If strFileName = "" Then 'no more files Exit Do End If LoopEnd Sub

Please let me know what I can do with this
I am a novice at coding...Just tell me where to put the code

Thanks

Ayyad

View Replies !

---

INSERT INTO With Summation And DSUM Difficulties
hello all,

i stumbled upon this site via google while i was trying to find a solution to my problems! i'm a pretty proficient pl/sql writer and this is my first time trying to learn jet sql so a lot of the things i'm used to in pl/sql can't be done in jet sql!

1) i'm trying to create a summary record from RR_FT_COMPONENT to RR_FT_TOT_PAYOUT. this is basically summing some payout fields by grouping by payee_id and period_id. from my research i've seen that jet sql doesn't like sum or group by's for updates. is it the same for doing select statements for insert into?

when i run the query i get the following error: "ms access can't append all the records in the append query. MS access set 0 fields to null due to a type conversion failure"

all of the fields on both tables have the same type. is this a group by and sum problem? i ran the select statement separately and it worked fine.

code:
INSERT INTO RR_FT_TOT_PAYOUT ( market, region, financial_center, payee_id, employee_name, manager_flg, lic_code, job_code, period_id, volume, cp_spread, ytd_spread, cp_comm, ytd_comm, prior_yr_flag )

SELECT max(rc.market), max(rc.region), max(rc.financial_center), rc.payee_id, max(rc.employee_name), max(rc.manager_flg), max(rc.lic_code), max(rc.job_code), rc.period_id, sum(rc.volume), sum(rc.cp_spread), sum(rc.ytd_spread), sum(cp_comm), sum(ytd_comm), max(prior_yr_flag)
FROM RR_FT_COMPONENT AS RC
GROUP BY rc.payee_id, rc.period_id;


2) in this next code i'm trying to sum spread information in the ft_txn_summary table for a given payee_id, market and period_id. i'm using dsum and have the appropriate joins to keys on the tables, yet this update sums ALL records in the ft_txn_summary table rather than summing the specific payees in the join. any idea on what's wrong or how to debug dsums?

code:
UPDATE rr_ft_component AS rc SET rc.cp_spread = Dsum("txn_spread","ft_txn_summary","ft_txn_summary.payee_id= " & [rc.payee_id] AND "ft_txn_summary.market= " & [rc.market] AND "ft_txn_summary.period_id= " & [rc.period_id])
WHERE rc.component_name='Total Revenue';


any help would be appreciated! thank you!

View Replies !

---

Difficulties In Designing Functional Forms And Tables...
Hi,

I am a newbie at Access and am basically designing my first database. This database is for a short term project and is designed to track the attendance of employees at acompany. Here is a brief description of the tables and forms in question.

EmployeeInformation - this table stores records of each employee, their ID, DOB and SIN.

AttendanceProfile- this table stores records of prolonged absence periods for each employee. A given employee can have many attendance profiles, meaning that this table is linked to the EmployeeInformation table via a one-many relationship. This table will provide detailed information about the status and history of each Profile. So for example if an employee misses work for 12 days due to an appendicitis operation, the exact nature and dates of this absence would be listed as a new profile. The primary key for this table is an autonumber field called ProfileID

ActionLog - this table stores the actions taken by company employees (if any) in response to each AttendanceProfile. It is connected to the profiles table via a one-one relationship with ProfileID being the foreign key in this table. Another main field is the ProcedureNum field which will list the number of each procedure taken for a given ActionLog record. This table is needed to track what the company has done/is doing to track an employees prolonged absence.

Allow me to illustrate with an example. An employee, John Doe misses 15 days in June 2001 due to an illness in the family. An AttendanceProfile record is then made for this period illustrating the exact nature of Mr. Doe's absence as well as tracking what official documents he has submitted (eg medical note, official company documents). Another matching record containing the same ProfileID is also made in the ActionLog table describing what the company has done so far to track this absence. So if an HR employee calls Mr Doe's physician to verify this illness or sends a letter requiring further documentation, each of these steps is listed in this log for this absence profile.

Sorry for the long-winded explanation, but better now than to have to clarify myself later. Here is where I'm stuck. I would like to set up two data entry forms, to create new AttendanceProfile records and related ActionLog records. Obviously, these corresponding records are linked directly by the ProfileID field. After completing the AttendanceProfile form, I would like the user to be able to click a command button to open another form (I am avoiding a subform in this case because my attendanceProfile form is huge and I cannot conveniantly fit a subform on it) to open a new form where they can enter Procedure descriptions and dates on the ACtion log. For each new procedure the user enters, an autonumber fields automatically increments the procedureNumber for that specific ProfileID.

Finally, my question to any of you is, assuming that I have already designed the AttendanceProfile table and form, how shall I design the ActionLog table AND forms to accomplish this exact task.

Once again, sorry for the excessive detail. Your assistance is greatlys appreciated.

Regards,
Mike J.

View Replies !

---

Spa System
I am new to Access and Ive created adatabase to control a spa system.
I have several tables: Customers
Employees
Types Of Haircut - Their prices
Types oF massage Their Prices

Now I want To create a form where i am able to choose customer, the employee performing the haircut or massage
The price should be displayed and the total AMount

How can I do that?
Should I create a Query and based on the query I create the form?

Please Help!!!

Thanking you all

View Replies !

---

System.MDW File
I am still a bit confused about the system.mdw file.
I create FE and BE on my local computer and change name of system.mdw to say Jobs.mdw, after backing up system.mdw I then join jobs.mdw with my jobs FE and jobs BE and set secutity settings, etc. I then place
FE, BE and Jobs.mdw all in the same folder.

I now email jobs database with jobs.mdw file to client which places FE, BE and jobs.mdw in Jobs folder on server. Shortcut for fe is placed on each local machine.

My question-Does Jobs FE and Jobs BE still access Jobs.mdw even though placed on another computer and possibly another version of Access?

Does the shortcut have to reference Jobs.mdw?

I need to understand this.

Thanks,

Jim

View Replies !

---

Payroll System
Hi All,

Need your expert advise. Is it ok to develop a simple payroll system using MS Access or do i need switch to Visual Basic?

Also, is there anybody can help what tables do i need to produce a payroll system, i.e. tblEmployee.


Thanks,

19Summer95

View Replies !

---

Reservations System
I am creating a database to room allocation in a small hotel. I want the system to check room availability between two dates. What would be the best way to construct this in Access?

Kev

View Replies !

---

Set An Equiry System
Hi, I am trying to implement my Database with an enquiry (or quotation) table and I am not sure I am heading in the right direction:

-We use assemblies made of components and the data are held together in the database with a 'Bill of Material' set up.
-A typical assembly is made of 1 or 2 bodies + Fasteners (straps and screws),
-The body price is calculated depending on his weight and an extra costs can be added with extra tooling and machining,

The costumer ask us for a quotation for various number of assemblies and we send back the estimation of the price,

I am really struggling to see if my tables are set right and this is what I came up with (attached rr.zip):
A main Enquiryfile Table is linked to an enquiryDetail Table which would record each individual item information for each Enquiry.
I have also added a Extracost detail table that would record all the Machining and extra pattern making cost for each assembly.
And finally, and that's what I am far to be sure about, a TblChildItemCost where the price of each components would be recorded (for each enquiry detail). The body price is based on his weight but also depend of various market exchange rates, that is the reason why I have created a table for the price of component for each enquiry.
I really hope someone can help me on that one...Thanks in advance.

View Replies !

---

System Tables
Hello,

My application I develop should retrieve some meta data from Access system tables. It should find:
- What tables are stored in database.
- What columns have each table in database.
- What relationships are between tables in database.
I opened systems tables of some .mdb file and took interest in such kind of information. But I got some problems by looking for what exactly I need. Please, could someone help me at these points:

1) I found that in system table MSysObjects I can find names and ids of tables created by user (for example by me). But how to determine that these tables are created by user and they are not let’s say a system tables?
2) I didn’t find any meta data about columns of tables! How to determine what columns has each table?

I created application which uses MS SQL Server to retrieve such a kind of meta information and there where no problems. But I stuck using Access.
I was looking for some information in Internet but ineffectively. Please, help me...

View Replies !

---

Hire System Help
I have two tables, one named Loan and one named DVD Details.

On my DVD details table i have a field name, on_loan with a data type yes/no.

I have created a form called LoanForm where i want to select a DVD with a combo box,

then select a Set loan button that will set the corresponding DVD on_loan field to yes(true), in the dvd form.

on the button i have the following code


Private Sub HireDVDButton_Click()

Update dvd
Set car.on_loan = True



End Sub


But it doesn't seem to set the on_loan field in the DVD table to true, which is what i want to do.

Sorry if it's not clear what i want to do. I am just a beginner.

Thanks in advance :)

View Replies !

---

Checking If The Same Name Is In The System?
I have a form where I enter student names, and it often happends that I enter the same student a couple times, Is there any way to let the user know that there is allready someone in the system with the same name, like a pop up message.:confused:

thx

View Replies !

---

Getting Into An Access System
Someone, who is no longer working at out organisation, created a system in Access which we are trying to get into, however the creater put on some security which will not let us open the system to alter. Is there a way of getting into this?

View Replies !

---