Add new record using first available non-numeric ID; update two ta

Access 2003, Windows XP Pro SP2

I have two tables, tblDed and tblVendor.

tblDed contains two columns (DedID and Avail).

The first column contains a list of all DedIDs allowed to be used.  The 
second is a Yes/No choice indicating whether or not that DedID has been used 
already.

tblVendor contains multiple columns with misc. data relevant to the Vendor.

I want to create a form that will accomplish three things:

1.  Use the next available unused DedID when a command button on a referring 
menu page (frmMenu, btnNew) is pressed.
2.  Mark the now-used DedID in tblDed as "No" - no longer available
3.  Upon completion of the form, save the results to tblVendor using btnSave

I'm stumped on Numbers 1 and 2.

Would somebody give me some pointers for Numbers 1 and 2?  Are they even 
possible?  Thank you for your time.
0
Utf
1/31/2008 5:01:01 PM
access.formscoding 7493 articles. 0 followers. Follow

2 Replies
1120 Views

Similar Articles

[PageSpeed] 46

In the new form you want to open, this code will find the first available 
DedID and update it a Not available.  I would suggest it be placed in the 
Form Load event.  
Put the Dim for strDedID at the top of the form module so it is visible 
anywhere in your form module code:

Private Sub Form_Load()
Dim rstDed As Recordset

    Set rstDed = Currentdb.OpenRecordset("SELECT TOP 1 DedID, Available FROM 
tblDed WHERE Available =True ORDER BY DedID;")
    With rstDed
        strDedID = ![DedID]
        .Edit
        ![Available] = False
        .Update
        .Close
    End With
    Set rstDed = Nothing
End Sub
-- 
Dave Hargis, Microsoft Access MVP


"Allison" wrote:

> Access 2003, Windows XP Pro SP2
> 
> I have two tables, tblDed and tblVendor.
> 
> tblDed contains two columns (DedID and Avail).
> 
> The first column contains a list of all DedIDs allowed to be used.  The 
> second is a Yes/No choice indicating whether or not that DedID has been used 
> already.
> 
> tblVendor contains multiple columns with misc. data relevant to the Vendor.
> 
> I want to create a form that will accomplish three things:
> 
> 1.  Use the next available unused DedID when a command button on a referring 
> menu page (frmMenu, btnNew) is pressed.
> 2.  Mark the now-used DedID in tblDed as "No" - no longer available
> 3.  Upon completion of the form, save the results to tblVendor using btnSave
> 
> I'm stumped on Numbers 1 and 2.
> 
> Would somebody give me some pointers for Numbers 1 and 2?  Are they even 
> possible?  Thank you for your time.
0
Utf
1/31/2008 6:05:00 PM
Thank you for your help.  I appreciate the assistance.

"Klatuu" wrote:

> In the new form you want to open, this code will find the first available 
> DedID and update it a Not available.  I would suggest it be placed in the 
> Form Load event.  
> Put the Dim for strDedID at the top of the form module so it is visible 
> anywhere in your form module code:
> 
> Private Sub Form_Load()
> Dim rstDed As Recordset
> 
>     Set rstDed = Currentdb.OpenRecordset("SELECT TOP 1 DedID, Available FROM 
> tblDed WHERE Available =True ORDER BY DedID;")
>     With rstDed
>         strDedID = ![DedID]
>         .Edit
>         ![Available] = False
>         .Update
>         .Close
>     End With
>     Set rstDed = Nothing
> End Sub
> -- 
> Dave Hargis, Microsoft Access MVP
> 
> 
> "Allison" wrote:
> 
> > Access 2003, Windows XP Pro SP2
> > 
> > I have two tables, tblDed and tblVendor.
> > 
> > tblDed contains two columns (DedID and Avail).
> > 
> > The first column contains a list of all DedIDs allowed to be used.  The 
> > second is a Yes/No choice indicating whether or not that DedID has been used 
> > already.
> > 
> > tblVendor contains multiple columns with misc. data relevant to the Vendor.
> > 
> > I want to create a form that will accomplish three things:
> > 
> > 1.  Use the next available unused DedID when a command button on a referring 
> > menu page (frmMenu, btnNew) is pressed.
> > 2.  Mark the now-used DedID in tblDed as "No" - no longer available
> > 3.  Upon completion of the form, save the results to tblVendor using btnSave
> > 
> > I'm stumped on Numbers 1 and 2.
> > 
> > Would somebody give me some pointers for Numbers 1 and 2?  Are they even 
> > possible?  Thank you for your time.
0
Utf
2/4/2008 8:48:00 PM
Reply:

Similar Artilces:

Linking records to a Service Activity
Hi there Is it possible to link an Opportunity and a customised entity of Property to a Service Activity? My client needs to know what the Opportunity contains (product, qty's), and what details we hold about the Property (lift access, parking) before they schedule the service activity. Any ideas would be great. -- Many Thanks Jan_s ...

V3 Availability
When will V3 be available to the general public? Is there a demo currently available for V3? Sorry, I'm referring to CRM V3.0 "KLD" wrote: > When will V3 be available to the general public? Is there a demo currently > available for V3? If you currently own CRM 1.2 or 1.0 with current software assurance, you can get CRM 3.0 in the 4th quarter of this year. New CRM customers will be able to get 3.0 in the 1st quarter of 2006. Of course, these dates are Microsoft dates, and subject to change. There is a new post on my blog that has details about 3.0, as well as a p...

Deleting records off Inventory Transfer/Pick List
Is there a way to select multiple records for delete off of an inventory transfer worksheet (worksheet 330)? We are using this worksheet to create a 'picking list' to transfer inventory from one store to another. When this worksheet runs, we end up with a great number of items that are value '0' Qty or '-' (negative) Qty at source. Before the worksheet can be approved, all of these lines have to be deleted, one at a time. The worksheet window does not allow for selection of multiple records. This can be hundreds of records and therefore is very time consuming. ...

Outlook Time Zone Data Update Tool
I'm not having any luck in finding more information about the Outlook Time Zone Data Update Tool and even a harder time locating the download. I've read article http://www.microsoft.com/windows/timezone/dst2007.mspx, it contains a link to the Microsoft Download Center but the tool doesn't list when you select the downloads for Outlook. I tried to do a search on MS for the "Outlook Time Zone Data Update Tool", with no luck locating the download. Does anyone know where to obtain a copy of the tool or if Microsoft has released it out to download. The MS articles state it...

Directly link item engineering data rev level to PO entry/update
Common industry standard is to note the revision level of the item ordered on the PO... this is required for ISO. There is no direct table link from item engineering data to the PO Entry/update regarding item's current revision level. -- Steve Laurenzano K-Rain Manufacturing ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based News...

Please Consult Before Posting: Unofficial FAQ Available #33
An unofficial FAQ for this newsgroup is available at http://umpmfaq.info. The source FAQ was last updated Saturday, June 26, 2004. You may save yourself and those willing to spend their time helping you both time and hassle by consulting this resource before posting what may already be a FAQ. The unofficial FAQ answers 140 of the most frequently asked questions in this newsgroup. Likewise, a comprehensive archive of this newsgroup is available at http://groups.google.com. Click on Advanced Groups Search; there you should specify newsgroup microsoft.public.money and some search terms you consi...

New to Access 2007
Hi, I am new to Access 2007 and having trouble working with a 2003(?) MDB. I opened it with the Shift Key down and I can see the tables, forms, etc. But when I select a form, I can only open it; the option to Design the form is greyed out and the entire Forms tab in the Ribbon is greyed out too. Help! Thanks much, Glenn "Glenn H" <glenn@pivotal-z.com> wrote in message news:1187024333.492672.70330@q4g2000prc.googlegroups.com... > Hi, I am new to Access 2007 and having trouble working with a 2003(?) > MDB. I opened it with the Shift Key down and I can see the tabl...

How do I print a select number of address records from Access
I'm trying to print just a select number of records for a mailing that I'm doing. I can't seem to figure out how to do this. Thanks. On Thu, 8 Nov 2007 12:23:04 -0800, robbie6 <robbie6@discussions.microsoft.com> wrote: >I'm trying to print just a select number of records for a mailing that I'm >doing. I can't seem to figure out how to do this. Thanks. Create a Query based on the table containing the addresses (and perhaps other tables). View the query's Properties (with the View menu item, or rightclick the background behind the table icons and sele...

print number of update records through sql job
I have update statement which updates column name (auditor_details) in a churn of 5000 records at a time of auditor table which contain 3.2 millions records. I have schedule this through sqljob. I'm looking over sql example to check for update records and gives a output and quit the jobs if no update records. Thanks in advance sqlnovice (sqlnovice@discussions.microsoft.com) writes: > I have update statement which updates column name (auditor_details) in a > churn of 5000 records at a time of auditor table which contain 3.2 > millions records. I have schedule this...

Existing Exchange 03 server to new Domain
We currently have an Exchange 2003 server, we would like to install a 2003 domain and make the Exchange 03 server part of the new domain. We would like to copy the user accounts for exchange to the new domain. Anyone have any info on doing this I am have some problems finding anything on Microsoft's website. you can only have one Exchange Org per AD Forest...are you trying to have two different ones or is this a child domain type of thing? -- Susan Conkey [MVP] "jpnels" <jpnels71@yahoo.com> wrote in message news:1147294933.261759.288080@u72g2000cwu.googlegroups.com.....

Dynamic chart not updating correctly.
Oh Wise Ones, I posted a similar question a few weeks ago, but I have more to add. I have a range (C4:C23) that dynamically pulls data from a sql server every 2 seconds and updates every 2 seconds. The problem is the chart I have attatched that graphs this range will not update even though I can see the values changing in the cells. What I have noticed since my last post, is that while I run WinXP/Excel XP, there is another station running Win2000/Excel 2000 that has a chart that updates dynamically. What gives? Thanks, Mike ...

Conditional Comment using Cell value as part of it
What I'm trying to do is the following: if cell(a1) is different from 0(zero) then, cell(b1) should have a comment that say, the value of cell(c1) is the payment, next line the value of cell(a1) was refund, next line Total for today = cell(c1)-cell(a1) Example a1 = $10.00 c1 = $30.00 B1 (comment): $30.00 is the payment (c1) $10.00 was refund (a1) Total for today: $20.00 (c1-a1) can anyone help me on that? thanks in advance ...

new email not displayed after running scanpst
I am running Outlook 2003 and this morning I got an error that there were file errors in my Inbox and to run scanpst.exe. I ran the scanpst.exe and fixed the file. When prompted if I wanted to backup the old file I chose yes named the file OutlookRepair.bak. Now when I download my email the mail is taken off my email server and the mail is not being displayed in Outlook. I have checked my Data Files location and it is still showing the same location as before the error. I tried importing this file and got a message that the file i was trying to import was the same as the destinatio...

Setting AutoNumber to Non-Default Value
I would like to use the AutoNumber data type within a field. However I would like the sequential autonumbering to begin from 6000 rather than the default value of 1(for the first record). Thanks for your help in advance. Jeff I suggest you may not want to use Autonumber for that use. Autonumbers are designed to provide unique numbers. It in not designed to provide numbers in order and for a number of reasons may not do so. As a result using them in any application where the user sees the numbers is likely to end up with confusion. There are other ways of providing the numbers...

Zip codes in mail merge
Office 2007 - used spreadsheet to create an address list; zip codes appear correctly in spreadsheet but when using spreadsheet as a mail merge document, the first digit disappears in the zip code field, i.e., 05401 becomes 5401. =text(a1,"00000") or use Word field formatting codes: Mail merge Excel to Word: a.. http://www.mvps.org/dmcritchie/excel/mailmerg.htm b.. http://www.mvps.org/word/FAQs/MailMerge c.. http://office.microsoft.com/en-ca/assistance/HA011164951033.aspx d.. http://sbarnhill.mvps.org/WordFAQs/CustomizingWord2002.htm e.. news:microsoft.publi...

Why is .NET 2.0 required to deploy an app using VC2005 C++ Setup project?
I just created a setup project for my mfc app. I noticed that in the prerequisites section the .NET 2.0 is checked by default. My app does not need the framework (at least this is what I think). If I clear the checkbox, build the project, and then try to deploy it on a machine with ..NET 1.1 the installer fails with a message complaining about missing .NET 2.0. I'm not sure if this the installer that needs the framework or my app. Are there any mfc8 dlls dependant on the framework? Thanks Hi, > I just created a setup project for my mfc app. I noticed that in the > p...

Add PA Inventory Transfers to eConnect
We have on client in particular that are trying to implement an integration that will ultimately create many PA Inventory Transfers. They would like to use eConnect to do that, however, only standard Inventory Transfers are available. One of two things could happen, PA Inventory Transfers can be merged into the standard Inventory transfers much the same as the PO's were, or create the eConnect schema for the PA Inventory Transfers. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion,...

Timeout error in incident.update
I have an incident.update line in my post callout. No matter how simple the update strIncidentXml is, I always get timeout error from the server. Afterwards I changed my mind and decided to use SQL update statement to update the incidentbase table. Again I get timeout expired message (ErrorMessage: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding) What can be causing this? Why am I not being able to update incidents? Thanks in advance for your ideas. ...

How do I get my new messages to populate when they arrive?
My new email messages do not show up in my inbox until I go to another folder and then back to the inbox folder. Then they show up. issue #2 I get a copy of my reply email in my inbox. How do I stop this? Do you have a firewall between you and your server? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.htm **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "Lonnie" <Lonnie...

In RMS PO add Level Pricing A, B or C for customization
Hi, Currently we are not able to add level price A, B or C in the PO template. If there is a need to be able to use any of the item level pricing it should be made available for customization purposes. Thanks, Mihir Shah Diviasoft, Inc. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in...

Add Movie to Outlook E-Mail Message via "Web Tools" toolbar--question
Using Outlook 2003 (in fact all of Microsoft Office 2003), and have Word selected as e-mail editor. Windows XP Pro with all updates. Recently I discovered on the Office web site a way to insert a movie (inline) to an e-mail message. I have one question about its implementation. If (in an HTML-formatted e-mail, and Word selected as e-mail editor) with the cursor in the body of the message, you click on "View", "Toolbars", you can select a toolbar called "Web Tools". On that is an icon that says "Movie". If you click on it you can add a movie to the e-ma...

Why does a drop ship PO need a Site ID for the line items?
What line item Site ID would I use on drop ship POs? On a normal PO, the Site ID is where the product will be inventoried; for a drop ship, it will never come into our warehouse so I'm confused why I would need to enter something here. The address to which the vendor ships the product is put into the Shipping Address ID (which makes sense)....I just don't get why you need a Site ID. -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 Bud, The Site ID is a required field and you will be asked to enter one, even when entering a drop-ship PO. Best rega...

Cannot resolve names to new users
Situation: EX2k on W2k svr. There is a DC svr, a file/app svr and the EX svr. I replaced the old EX server(1 Oct) and kept name the same per Q297289. Mail is flowing. Now the Domain Administrator mail account does not resolve. New user account names do not resolve either. I noticed in license manager that a second instance of Exchange2k is showing and transferred the licenses to it. The original instance is: Microsoft Exchange 2000 Server the newly installed instance is MSExchangeIS 6.4417 All updates and sp's were applied before bringing the new server online. Today I ra...

How to use Find (Ctrl-F) to find non-alpha
Is there some way to search a sheet for anything BUT a-z or A-Z? tx ;-) Finding numerals or a mixture of numbers and letters (and other characters) works ok for me. What are you having trouble with? On 10/22/2010 16:31, Heather Mills wrote: > Is there some way to search a sheet for anything BUT a-z or A-Z? > > tx ;-) -- Dave Peterson On Fri, 22 Oct 2010 17:54:28 -0500, Dave Peterson <petersod@XSPAMverizon.net> wrote: >Finding numerals or a mixture of numbers and letters (and other characters) >works ok for me. > >What are you having trouble with? > >...

RMS installtion ID
This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C81D68.50C214C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, Does any one know how RMS create installation ID? When I tried to = register RMS 2.0 by phone, the customer support representative ask me = the installtion ID. It 's different from Product key and product ID. Thanks ------=_NextPart_000_000A_01C81D68.50C214C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C...