By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,481 Members | 979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,481 IT Pros & Developers. It's quick & easy.

Autoinsert Next Available Value From Lookup Table

P: 1
Hello everyone! I'm new here, so go easy on me ;-) I have a pretty good skillset with VBA for Excel, but now I'm venturing out into Access. So if the solution I'm looking for requires VBA (and I'm sure it will), a little extra TLC would be greatly appreciated.

I have a table (AccessCodes) with three fields: AccessCode (PK), Available(Boolean), and ActivateDate. All AccessCode values are unique. I use this table as a lookup table for my main table which is called "EligibleCustomers".

As each record is added to the EligibleCustomer table, I would like Access to automatically assign the customer the next available AccessCode in the lookup table.

I would imagine that it first start with making a simple SELECT query on the Lookup table with Available = True. The query would then be used to link to EligibleCustomers as its lookup source.

The procedure I'm unsure about is how to automatically assign the next available AccessCode to the customer, and then mark the AccessCode's Availability to False.

Your helps is greatly appeciated. Thanks.
Aug 15 '07 #1
Share this Question
Share on Google+
1 Reply


FishVal
Expert 2.5K+
P: 2,653
Hello everyone! I'm new here, so go easy on me ;-) I have a pretty good skillset with VBA for Excel, but now I'm venturing out into Access. So if the solution I'm looking for requires VBA (and I'm sure it will), a little extra TLC would be greatly appreciated.

I have a table (AccessCodes) with three fields: AccessCode (PK), Available(Boolean), and ActivateDate. All AccessCode values are unique. I use this table as a lookup table for my main table which is called "EligibleCustomers".

As each record is added to the EligibleCustomer table, I would like Access to automatically assign the customer the next available AccessCode in the lookup table.

I would imagine that it first start with making a simple SELECT query on the Lookup table with Available = True. The query would then be used to link to EligibleCustomers as its lookup source.

The procedure I'm unsure about is how to automatically assign the next available AccessCode to the customer, and then mark the AccessCode's Availability to False.

Your helps is greatly appeciated. Thanks.
Hi, there.

The particular solution strongly depends on your interface. But as avery basic example I could sugget you the following
Expand|Select|Wrap|Line Numbers
  1.     Dim lngAccessCode As Long
  2.  
  3.     lngAccessCode = DLookup("[AccessCode]", "[AccessCodes]", "Available=True")
  4.     With DoCmd
  5.         .SetWarnings False
  6.         .RunSQL "UPDATE AccessCodes SET AccessCodes.Available = False " & _
  7.             "WHERE AccessCodes.AccessCode = " & lngAccessCode & ";"
  8.         .SetWarnings True
  9.     End With
  10.  
Aug 15 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.