I have extensive experience of other SQL databases but, in Access I don't know how to access the SQLCODE (as it would be in other DBMSs) to see if my query worked and if it did then how many rows were updated.
Anyone speak my language?
Hi Folks,
I'm working on an order management database for a small company and have an order details form which I hoped to list each product of the order. As each customer has a different price per product I have a combo (based on a query) to choose the product and display the unit price (working fine) - the quantity and product ID are also input on the form and stored in the order details table (un-tested). My problem is on the first line of the order you choose the product and it and relevant details are shown, however, on line two, three etc when you choose the product - the other lines change to this too. How do I fix this???
Many Thanks in advance for help given!!
Mary
I am almost finished this project. :p There are just a few things left that I would like to do and I need some help.
1. Add an email notification when data on the form has been edited
2. Filter the form for only OPEN orders, status not equal to 6 or 7
3. When an order is closed (status field) I would like to default the "Date Closed" to today, but allow the user to edit the date if need be.
4. Once the status is closed and the date closed is entered, ask the user if they would like to create a followup order.
I have most of #4 done. I added code on the After Update event on the Status field to set the Date Closed to today and then disable the control, then ask if they would like to create the followup order. The users would like to be able to modify the date closed if they don't happen to do the entry until the next day. I have tried adding my code to the After Update of the date (which is updated with a pop up calendar), but once I select the date from the calendar, the focus stays on the date and the user is not prompted to create the followup order.
Any help you can offer would be wonderful
Thanks
Pat
Hello!
I have a problem figuring out a solution for following problem.
I have a database of details and their properties.
All the details have been described in one table.
As there is a need to describe groups of details I have found myself in a hard situation that needs to be resolved.
The structure of described detail groups is as follows:
Product
Detail Group1
Detail groupx etc.
Detail4
Detail groupx etc.
Detail groupx etc.
Detail1
Detail2
Detail3
Detail Group2
Detailx
Detailx
Detail groupx etc.
Detail groupx etc.
Is there a way of decribing my products in access so that there are no limits of how many groups does a product have and how many levels of groups the product have.
Thank you
Hi,
I have not used Access for over 10 years! I am using Access 2000 to look at tracking cow weight over time!
There are 2 tables:
- main table
- weight table
I want to set the main table's January weight to a value from the Animal weight table (logical expression):
UPDATE [main table]
SET [main table].Jan07 = [Animal weight].[Kg]
WHERE [main table].[Line no])=[Animal Weight].[Line no] AND
[Animal weight].[weigh date]= 10/01/2007;
I have tried several times to get this working, but it tends to prompt for the weight to be given as an input.
Any ideas?
many thanks
Lactaman
Hi All,
I am rather new to this and I must admit I am getting everything completely wrapped around my head!
I am developing a database that stores technical support requests received and their solutions. I have three main tables :
Problem - stores all the details of the problems received
Client - stores the address details of the client which is then associated with the 'Problem' table
Company - stores the name of the company which is then associated with the 'Client' table. This is a separate table as different clients can work for the same company.
At present, I have a 'problem data entry' form in which I enter all the details of the problem. At the top there is a section where I can do a search for a particular client and company and the details appear in the respective text boxs on the 'problem data entry' form. If the client does not exist, I have a button that I use to open another form which allows the entry of the new client's details ('New Client'). The user can choose which company this client works for by using a combobox. If the company is not already in the database, I have another button for opening a 'New Company' form!
Its all a little convoluted and it does not work particularly well to be honest. When I add a new company, I need to move forward a record and then back in the 'New Company' form in order that the new company information is saved correctly.
The company selection combobox in the form 'New Client' is then updated with the new company name BUT I need to actually open the combobox and select the company name myself for it to work properly! Once again I also need to move forward one record and then back again in order to properly save all the information before I can close the form.
What would be nice is to do a search for the the company in the combobox on the 'New Client' form, if the company is not found, it automatically adds the company to the database whilst updating all the necessary links. Hence getting rid of the 'New Company' form. I would also like to get rid of the problem of having to move forwards a record and then back again.
Any ideas? I find my method to be rather poor, there must be a simpler way of doing this!
Many Thanks,
Lee
Hello,
I have not used access that much, but what I am looking to do is basically have 2 details sections within a report that will have different records in them.
I first used a subreport but that really did not work as intended. It left large gaps in between the records and just plain didnt work.
Here is a image of what I am trying to achieve:
hxxp://img379.imageshack.us/img379/9471/accesshelpnx9.gif
As you can see theres two different sections. One is for regular campaigns and one is for extended campains. There is a field in the table that has the extended date. For the compaigns that field should be blank or null and for the extended campaigns it is not blank or null.
Is there anyway to achieve this in Access 2003, if so can you tell me or describe me the steps?
Thank you!
Basically, our goal is to create a database that contains the information of previous girl scout info, just in case they decide to return, and also to have drop-down boxes in the form so when you select a returning girl scout, their info is already in the system and all the fields will be automatically filled with the info. i have thge link to the database once again
https://s38.yousendit.com/d.aspx?id=0TV74WKE03KN8194HDI0C7V2Z6
I have got a form with summarised data (of records) on view for people to look at. (this is a continuous form). For each record I would like to have a button that displayed another form with more details on it. So by pressing the button opposite a particular record it would take the Id value of the record and open another form (with more details) at that particular record.
I would be very interested in seeing this done with a macro if possible but beggers can't be choosers!
Hi There
I am wandering if anyone has a solution to this problem i has basically have 2 tables 1 called departments, with the fields: Dep No, Dep Name, then i have another table called Items, with the fields: Item No. Item Name, Item Price, Department.
What i want to do is create say 10 command buttons and create an array of them i know sort of how to do it in visual basic and all the 10 command buttons name is the same and an aray is created but i dont know how to do it in access i know that it doesnt let you create an array but i have seen it done somehow in a project that i came across.
so basically what i would want to have done is on the form load the 10 department buttons captions are loaded from the table departments and when a department button is clicked its is linked to items where i ahve created say 20 command buttons called items.
I dont know it it makes sense what i have written but any help on this matter would be very apreciated it pr if anyone knows of any examples as i have been stuck on it a while
Many Thanks
Dear all, please excuse the fact that this is not an explicit problem I have, but more of a general query.
I have an Access database (2003) with a load of contact details in it. Including various fields for each contact, such as their email, services they offer, where they are, main contact name etc.
Some of these contact details will be out of date and I want to make sure that all the contact details are up-to-date.
I am in the process of setting up a mail merge with a Word document which will output to Word their relevant details, and this can then be e-mailed to each of the contacts with them to update and e-mail back again. Someone would then manually update each of the contact details.
This is quite a time intensive way of doing it, and I wondered is there a way of automating the process? I don't have the time to put the whole of their details on a website so they can update it over the web, but is there a way that access could possibly read the return Word document to automate updating their details? Or is there another easier way of doing it, without using word and using something else such as Excel or something.
If anyone has any experience in doing this kind of thing I would be really glad of some tips.
Many thanks
Dear Guru's,
Happy New Year 2008.
:confused:
I have a task of implementing a fleet fuel consumption. I have made a form where user logs in previous odometer and current odometer reading. I have been challenged to make the system to automatically use previous odometer reading next refuelling. Please assist.
Joseph Njoroge
Hi i hope someone can help. :)
I have created a databse with three tables that are linked (customers, quotes and invoices). I have my main form (customers) with two sub forms (quotes and invoices).
Is it possible to create a query to show the data just from the current form that i am viewing?
I would appreciate if you could help.
Hi everyone, I am new to this so bear with me.
I currently have a form with textboxes and 2 combo boxes. I have a submit button at the bottom of the page and would like this to take all the infomation from the form and add to a master table.
I was hoping to be able to create a pop up message to say "This item has been added to the database"
Can anyone help point me in the right direction?
Many Thanks!
Hi everyone, I am new to this so bear with me.
I currently have a form with textboxes and 2 combo boxes. I have a submit button at the bottom of the page and would like this to take all the infomation from the form and add to a master table.
I was hoping to be able to create a pop up message to say "This item has been added to the database"
Can anyone help point me in the right direction?
Many Thanks!
Part of a database I'm developing stores details of events. Each event is funded from a variety of different sources.
There are three tables involved in making this work:
tblEvents - which stores the main event details
tblFundingOptions - which stores all the possible funding sources
tblEventFunding - which links the two together.
I have created a form to access all of this, with the information from tblEvents at the top and the Funding details on a subform.
I have created a query based on tblEventFunding which adds up the total amount of funding allocated to each event. I want to display this total on the form I've developed.
Can I do this without creating a second subform to pull the details in from the Query? I've tried creating a text box and linking it to the appropriate query, but it just displays #Name! - presumably because it doesn't know which record from the Query it should display, and I don't know how to tell it!!
Is what I'm trying to do possible, or do I need another subform?
Many thank in advance,
Gary
Hello,
I have an invoice form where ofcourse I have a invoice detail subform, I got everything working fine so far, but now I need to do an update to this form and I dont know how to do this.
I have the following tables on my DB:
Clients
Products
Departments
Invoices
Invoice_Detail
Now each product will belong to a department, let say 1" PVC Tube belongs to the Department Pluming.
This is what I need to do, I open the invoice form enter all the invoice information, than I goto the invoice details, there is the tricky part, I want to select from a combo box the department of the product I will sell, once I selelct the department I want to selelct the product... The problems is that I only want to be able to see the products that belong to that department I picked.
How can I do that?
thanks if there is any example db will be great...
Daniboy
Hi,
I have a couple of identical databases in Access 97, they have lots of forms. My users use the two different versions of this database, but linked to different sets of data depending on what they are doing.
I distinguish between the two versions by making the background colour of the log on and Switchboard forms different. It would be nice if I could cause the background colour of the other forms to match the Switchboard without having to go through and change each form individually.
Can someone please give me a clue as to how this can be done?
I know using Me.Detail.BackColor = RGB(100, 100, 255) would work, but I can't see how to a) get the RGB values from the Switchboard.
Regards,
Bernard D
Hello can anyone help me with my prob. I am trying to call up records from two different tables to display on the same page but haven't been able to get it to work. I have a table called members and another called Messages. The page that the info is called up from is called viewtopic. I can get the topics to display properly but without the members stats with this query:-
'sqlString = "SELECT * FROM Messages WHERE TopicID=" & topicID
but I need the member stats to be displayed too so I developed this query which doesn't work at all:
sqlString = "Select Messages.Topic, Messages.TopicID, Messages.BSection, Messages.MesDate,Messages.Message, "&_
"Messages.Edited_On, Messages.Message_ID, Messages.Member_ID, Messages.Username"
sqlString = sqlString & " FROM Members INNER JOIN Messages "
sqlString = sqlString & "ON Members.Members_ID=Messages.Members_ID"
sqlString = sqlString & " WHERE Messages.TopicID=" & topicID
sqlString = sqlString & " ORDER BY Messages.MesDate "
This comes up with No value given for one or more required parameters which I'm guessing is for the members_ID but I'm not sure. There is a one(members) to many(messages) relationship on the two tables. Can anyone tell me where I have gone wrong in the query?
thanks
Grant
I have a form which is used for data entry for a new record only. I then wish to print some of this record's details on a report, using a command button on the form. At present I cannot filter to get just the current records details on the report - I am getting the report containing all records in the table.
Thank you
I am trying to page break my report to have it where the first name and surname of the employee has a separate page with the details of each appointment.
I have tried to do it via format on properties but it does not work..
I have attached an image of how the report appears.
Hi I wonder if someone could help as I'm relatively new to this. I have tried to search for my answer but I'm not finding stuff that answers my need.
I have 1 table with products (tblProds) and 1 with stocklevels (tblprod_Stocklev). the PLU field is in both tables
tblProds tblprod_stocklev
PLU PLU
Descritption StockLevel_1
CategoryMaster StockLevel_2
CategoryGroup StockLevel_3
Category StockLevel_4 etc....
My query filters using 3 combo boxes using the category fields and the Option group sets its value to the corresponding number in the stocklevel table from a form (1 for stocklevel_1 etc). I have the category filter working as I need (ie selecting all records if left blank)
So to the question: How do I get my query to retrieve only the stocklevels for the required branch? :confused:
Any help/advice is much appreciated :)
Hi there,
I am fairly new to access. I have searched high and low for help on this particular topic. Its something pretty simple, I just don't know exactly how to do it.
Basically, the user will have a client selected from the main form, when they hit the button to go to another form which has details on services done on that client at a specific date, i want the new form to have the clien'ts ID present and a fresh record present in which I can enter the date of visit, what was done, recommendation, etc.
Right now its popping the new form open with the client, but it displays the last record that was typed in that new form. Does anyone have any suggestions or ideas on this?
Thanks
I tried searching, to no avail. I've attached pics to represent my issue a bit clearer. I have a form that I want to be my switchboard, but my clients all have different resolutions, and this is where I run into the problem. I want the switchboard to not only be maximized, but appear maximized.
Here is the design view: Linkage (http://www.martinaustin.net/images/issue_design_view.jpg)
Here is the form view: Linkage (http://www.martinaustin.net/images/issue_form_view.jpg)
I want the date and bottom line to always be at the bottom. This works fine by simply using form headers and footers. The issue is that the Admin button will appear near the center (vertically) when viewed on a computer with high resolution, instead of just above the bottom horizontal line as I intend.
Does anyone know of a way to fix this?
I have stumbled through making a few new forms and tables for my new job, and have done alright so far (even if it takes me forever to do so), but there is one thing that keeps tripping me up. I just cannot figure out how to do it, even by looking at the designs and properties of other tables/forms.
Part of my form requires me to input location details. A table is setup with every City, State and Zipcode for usage on the form. Our old form was designed to auto-fill (is that even the right term?) the City and State fields upon entry of a Zipcode. These are all coming off the same table, and it works on our old form, but I cannot seem to replicate it on the new one.
I have 2 other issues with the form, and they are also both related to this auto-fill/auto-lookup hangup. Another table is set up with employee IDs, and their corresponding Last name, First name and Middle Initial. I'd like to be able to type in the ID and have the next field automatically enter the corresponding name. Similarly, I need to enter certain other data, and I would like the next field to fill in a short description of said data.
Can anyone help me or point me in the right direction? Like I said, I've pretty much made everything else work by learning from the designs of other tables/queries/forms we have, and by trial and error. But this final hitch seems insurmountable.
I've got a form which holds data for employees, fname, lname, ..... and an entered date which defaults to now().
On the before update event, i have the following.
Code:
If DCount("*", "trainingdata", "[Empid]=" & Me!EmpID & _
" And [subjectid]=" & Me!SubjectID & _
" And [trainingdate]=#" & Me!TrainingDate & "#") > 0 Then
MsgBox ("This record already exists")
Cancel = True
Me.Undo
Me.SubjectID.SetFocus
End If
End Sub
With the above vba code, a msgbox pops up if the record combination already exists. What I would like to do is in the same msgbox have the "entered" date display and make the msgbox say something like.
This record already exists and was entered on 6/18/2015, [entered]...