I've got it sorted. I opened the database this morning, deleted the existing queries and re-wrote them and it works as I want it too now!
Thanks for your help :)
ashenton
Last Activity: Jul 12 '10, 01:27 PM
Joined: Oct 9 '08
Location:
-
ashenton replied to How to subtract values between two queries if control value is the same (Access 2003)in AccessGaaa!!!
I've done that and it works... Unless I issue the same items of stock on different orders...
E.g.
I have 5 40gb HDD's in stock.
I issue 2 40gb HDD's on order 1
I issue 1 40gb HDD on order 2
The total remaining should be 2 40gb HDD's
However... Access takes it upon itself to multiply the received stock by the number of orders that item was issued on and THEN does...Leave a comment:
-
ashenton replied to How to subtract values between two queries if control value is the same (Access 2003)in AccessHi,
Thanks for that, it works on the calculation side.
I now have a new problem!
I've told the query to group by stock item, so that no matter how many of an item we receive, it only display the item once with the total stock next to it.
E.g.
On the 1st we receive 10 40gb HDD's
On the 15th we receive 10 40gb HDD's.
I'd like it to show:
...Leave a comment:
-
ashenton started a topic How to subtract values between two queries if control value is the same (Access 2003)in AccessHow to subtract values between two queries if control value is the same (Access 2003)
Firstly: I've a fairly inexperienced access user!
I'm developing a simple stock control database and need to be able to show a total stock for each item.
So far I have two queries, both are grouped by the unique item of stock.
The first query shows how many items of stock we have received. All unique items of stock are always displayed even if there are none in stock.
The second query shows... -
Hi,
Thanks for that, it's great.
There is something I'd like it to do slightly differently...
When you create a new entry, it goes to a form with no records. Once you have created the new entry you have to exit and then re-open the form to view the existing entries. Is there a way of getting it to just continue at the end of the records as if there was no code at all?
I'd also like it to...Leave a comment:
-
Thank you very much :)Leave a comment:
-
That's good :)
Is there some way of having the DAMS Number Search field built in to the DAMS Number field?
What I'd like to have it do is if you create a new record but the DAMS Number already exists, the DAMS Number field does the job of the DAMS Number search field and tells you it already exists before it takes you to the matching record.
If it doesn't exist, I'd like it to let you enter the new information...Leave a comment:
-
Thank you very much :)Leave a comment:
-
Here is the attachment, sorry it's taken so long!...Leave a comment:
-
I've found where you mean now, thanks for that :)
I'll have to do the upload from home as it's been blocked by the corporate network lolLeave a comment:
-
Hi,
I don't seem to be able to attach anything. There is no browse button at the bottom of the message edit box :(Leave a comment:
-
Nope, that doesn't work either. It comes up with another 2498 error which VB attributes to
:(Code:DoCmd.OpenForm Me, , , strFilter
Leave a comment:
-
By the way, the DAMS Number field in the table is set to text.Leave a comment:
-
Hi again,
Sorry for the late reply, I been off sick.
I tried the code. With 'tmpID As Long' I get the same 2498 error. When I change the line to Integer I get a nic error!
Run-time error '6'
Overflow
VB shows the error in line
...Code:tmpID = Nz(DLookup("[DAMS Number]", "DAMS Stock", "[DAMS Number]=" & Me!DAMS_Number), 0)Leave a comment:
-
Hi again,
Sorry for the late reply, I been off sick.
I tried the code. With 'tmpID As Long' I get the same 2498 error. When I change the line to Integer I get a nic error!
Run-time error '6'
Overflow
Any ideas? lolLeave a comment:
-
I tried the first change and got the same type mismatch error as before.
When I tried the second change I got another 2498 error in
:(Code:DoCmd.OpenForm Me, , , lngFilter
Leave a comment:
-
I've got a whole new error now lol
Run-time error '13':
Type mismatch
It occurs in this line:
Code:lngFilter = "[DAMS Number] = " & tmpID
Leave a comment:
-
It gives the same error in the same line :(Leave a comment:
-
Hi,
The existing code gives the following error:
Run-time error '3314':
The field 'DAMS Stock.####' cannot contain a Null value because the Required property for this field is set to true. Enter a value in this field.
Where #### is the item on the form whose property is set to required.
I need certain values to be entered when creating a new record where one does not exist,...Leave a comment:
-
I set the required property for DAMS_Number to no but there are several other fields which are required (as well as DAMS_Number) which create the 3314 error.
DAMS_Number is the stock asset number and is required for more or less everything I do. It is the primary identifier on our stock so must be included in the database :)Leave a comment:
No activity results to display
Leave a comment: