Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 7th, 2007, 09:02 AM
Authorized User
 
Join Date: Jun 2007
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Autocount in Tabel A that counts from Tabel B

Hello,

I tried search it but i cannot find such a thing. Let me explane myself:

I have a Table A and Table B

Table A is filled with "requests"
Table B is filled with "requests" that have been approved

When an item from Table is approved it goes from table A to B. When you push the button_approve the data goes from A to B and then Deleted from A.

Now I need an automatic name creater. This means that if someone selects the form "F_Request" from the dashboard that the form field [Issues].[reference] (= primary key) automaticly creates PRO0001 etc etc ... but ... The form has to go and look in Table B [Issues].[Reference] for the current number and then put in Table B [Request].[Reference] Number + 1... This is because the records in Table B gets deleted and it starts from PRO0001 all over again and that is not correct.

I hope all is clear and you understand what I want. Maybe you can give me the function name or a link to page where something like this is explained, or explain it here.

Thank you in advance.
Bjorn
 
Old June 7th, 2007, 11:46 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Hi,

   This is easy.

SELECT TOP 1 TableB.FieldID
FROM TableB
ORDER BY TableB.FieldID DESC

   This will always return the highest value in the column. Then add 1 to it.

Did that help?



mmcdonal
 
Old June 7th, 2007, 12:22 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, you can also use MAX


mmcdonal
 
Old June 8th, 2007, 04:20 AM
Authorized User
 
Join Date: Jun 2007
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much,, it does the trick

 
Old June 8th, 2007, 07:54 AM
Authorized User
 
Join Date: Jun 2007
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It did not exactly what I wanted so I changed the approach and did this.... Now it works great: Maybe someone can benefit from it aswell..

Dim A, B, C
        A = DMax("[ID]", "Issues")
        B = DMax("[ID]", "T_Request")
        If IsNull(A) Then A = 0
        If IsNull(B) Then B = 0
    If A > B Then
        C = A + 1
    Else
        C = B + 1
    End If
  Me.ID = C






Similar Threads
Thread Thread Starter Forum Replies Last Post
Search and match record from two tabel delion Pro VB Databases 1 May 15th, 2007 11:51 AM
lookup value in tabel and store in continuous form Vince_421 Access VBA 1 May 22nd, 2006 09:22 AM
Replacing tabel civa Access VBA 1 January 24th, 2005 08:41 AM
trace tabel & replace the same civa BOOK: Beginning Access VBA 0 January 21st, 2005 01:51 AM
Getting Counts [email protected] SQL Language 2 October 27th, 2004 04:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.