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

Reading Table Information in VB

P: 60
Version: Access2003

I have 3 tables in a database named:
tblDept - stores the possible departments and next box number for
that department (if there are currently 6 boxes in the Q dept,
then the next box number for Q is 7.)
tblBox - stores the boxes number (such as Q-0001, Q-0002, R-0001, etc)
tblContents - stores the contents of a box
I have a form (linked to tblBox) and a subform (linked to tblContents). On the form, I have the boxes number (stored in two seperate fields, the department designation such as Q and the reference number such as 0001). I have a combo box on the same form that gets values from tblDept.

When the user selects a department from the combo box and clicks a "Create Box" button, it takes the designation of the department and stores that in the department designation textbox through VB.

I need to draw the current maximum box number from the relavant field in tblDept, store that number in the reference number textbox, and then increment the number in tblDept. How would one go about this?
Mar 13 '07 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 15k+
P: 31,347
To answer this properly one would really need the table MetaData (Example provided below), but in concept, you don't really need the Max Box # stored (and updated and wrong and ... etc) in the Department table.
Simply populate the field with a DMax() + 1 of the Box Numbers used in tblBox records for that particular department. Without the control names on your form and the table MetaData it's hard to put together the exact format required, but it's certainly a better idea to work it out on the fly than to try and store it in the tblDept record.

Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Mar 13 '07 #2

P: 60
txtAbbr (one to many with tblBox.txtDeptAbbr) {Primary}
autoNumBoxID (one to many with tblCont.numLinkedBoxID) {Primary}
autoNumInvoiceID {Primary}
frmEnterData (SELECT * FROM tblDept, tblBox, tblCont; )
cmbDeptName (SELECT tblDept.txtName, tblDept.txtAbbr FROM tblDept ORDER BY txtName, txtAbbr; )
When cmdCreateBox is clicked, I need to take the current value in cmbDeptName, find the tblDept.txtName that is equal to that value, get the tblDept.txtAbbr linked to that value, and store that in txtDeptAbbr. I then need to take the tblDept.txtAbbr value and find the current largest value in tblCont.numReferenceNo where tblCont.txtDeptAbbr == tblDept.txtAbbr.
Mar 14 '07 #3

Post your reply

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