0

I have a form that is being used to create entries with different sequential fields. I'm currently using DLookup in order to do this, but I am running into some issues.

 Me.txtProgramID.Value = DLookup("ProgramID", "tblMain", "Program = Forms!Form2!Combo.Value") + 1
 Me.txtProgramNumber.Value = DLookup("Number", "tblMain", "Program = Forms!Form2!Combo.Value") + 1
 Me.txtSequence2.Value = DLookup("Sequence2", "tblMain", "Program = Forms!Form2!Combo.Value") + 1
 Me.txtSequence1.Value = DLookup("Sequence1", "tblMain", "Program = Forms!Form2!Combo.Value") + 1

There are four different values: ProgramID, ProgramNumber, Sequence1, and Sequence2. Everytime a new record is added, based on the contents of Combo, the contents of the new field should be the previous field + 1.

Lets say the contents of Combo is A and that the values for ProgramID, ProgramNumber, Sequence 1, and Sequence 2 are all 1. The new record for A should have them all as 2. The problem I'm having is that instead of DLookup finding the most recent entry, it is capturing the original, meaning that instead of having 1, 2, 3, 4, etc I have 1, 2, 2, 2.

Really what I need to know is how to make DLookup grab the most recent record in regards to the respective profiles.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
user1706975
  • 325
  • 2
  • 5
  • 11

1 Answers1

1

You might be able to get away with DMax, but once you have more than one user, all bets are off:

Me.txtProgramID.Value = DMax("ProgramID", "tblMain", _
     "Program = Forms!Form2!Combo.Value") + 1

ProgramID should probably be an autonumber, so there is no need to get the next number, it is handled autmatically. However, there is no guarantee that an autonumber is the previous number +1. I am not sure why you would have a programID and a program number.

A proper sequential number is a but more complicated: Access VBA: Find max number in column and add 1

Community
  • 1
  • 1
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • I have a ProgramID and a ProgramNumber because ProgramID is not a number, it's a text string. The user selects a ProgramID from the Combo box, which determines which number line we will be adding to. Each program has to have its own independent number line. – user1706975 Feb 04 '13 at 19:14
  • Which is your key field? An autonumber keyfield is usually the easiest. – Fionnuala Feb 04 '13 at 19:17
  • Wait, that's a lie. The Program name is not the program ID, it's just another number string. I'm even confusing my self at this rate. I'm not really using a key field at the moment. I can't rely on an autonumber because it may not always be +1. – user1706975 Feb 04 '13 at 19:18
  • if you are getting confused, are you sure that the database design is normal http://r937.com/relational.html ? – Fionnuala Feb 04 '13 at 19:21
  • Yeah I have the design ironed out. It's pretty simple really, I just have a bunch of different things going on and I confused my self. Anyways, I'm going to use DMax for the time being as that seems to work in a single-user environment. I will however continue to research the issue. Thanks for your help! – user1706975 Feb 04 '13 at 19:22