DMAX User Input on a Form?

[rprieto]

How can I allow a user to define the "fldProgramID" below within a form?

The following works but I must force the fldProgramID=1 to find the last record number and increment by 1 to insert a new record for a specific ProgramID. I do not want to use the Autoincrement since it is possible to have the same RecordNumber for different ProgramIDs.

=DMax("fldRecordNumber","tblRecord","[fldProgramID] =1&quot+1

VBA coding uses the standard: "DoCmd.GoToRecord , , acNewRec" to insert a new record.

What I would like to do is something like this:
1) Provide the user a "Insert New Record" button that, upon button Click askes the user to select from a predefined list:
(Which Program?: A, B, C, or D?)

2) Passses the user selected Program_Input to the following:
=DMax("fldRecordNumber","tblRecord","[fldProgramID] =Program_Input&quot+1

3) Creates a New Record based on the above input.

I am new to Access 2002.

Thanks!

 

[RottPaws]

It looks like you're pretty close in your code. I assume your Program list is a combo box named fldProgramID. Provided this is the case, you just need to concatenate the value into your DMAX statement.

=DMax("fldRecordNumber","tblRecord","[fldProgramID] = " & Program_Input)+1 (notice the removal of the quotes after the control name)

The above should work in the value of the Program_Input control is a number. If it's text, you'll need to enclose the value in quotes.

You may also want to look up primary keys in the help files and read about multiple-field primary keys. It sounds like this is what you're trying to do.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[rprieto]

Where do I place this? I had placed it in the Default field value of the form for fldRecordNumber. If it needs to be in VBA how do I do this in combination of adding a new record?

=DMax("fldRecordNumber","tblRecord","[fldProgramID] = " & Program_Input)+1

The "Program_Input" was just a place holder. I need help in getting the user input value (Program_Input) to be placed into this DMax statement.

Thanks.

 

[RottPaws]

Check out the combo-box wizard to create the drop-down list. It will walk you through creating the list either from a table or from a list of items you type in manually.

Since a "Program_Input" is required, adding the =DMAX statement to the default value property wouldn't work. You could put it in VBA in the AfterUpdate event for the Program_Input control. Look up Events and AfterUpdate Event in the help files.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[rprieto]

Please tell me how to do this in VBA. I need an example.

The default Add New Record is currently as follows:

Private Sub cmdAddDATARec_Click()
On Error GoTo Err_cmdAddDATARec_Click

DoCmd.GoToRecord , , acNewRec

Exit_cmdAddDATARec_Click:
Exit Sub

Err_cmdAddDATARec_Click:
MsgBox Err.Description
Resume Exit_cmdAddDATARec_Click

End Sub

Where and how do I insert the DMax statment to find the last record number for a user specified fldProgramID? I know how to use the combo box but what exactly do I type in the AfterUpdate box?

Maybe I don't need to mess with the VBA code at all and everything can be placed in the AfterUpdate area...
Curious how it captures the selected value in the combo box and passes it onto an AfterUpdate command.

Thanks for your patience.

 

[RottPaws]

Look up 'Events and Event Properties Reference' in the help files and from there check out the info on the AfterUpdate event. It will tell you what the even it, how to use it and provide an example.

Say you have a combo boxe called Program_ID and a text box called RecordNumber.
Leave the default values for both blank. Then create an Event Procedure for the AfterUpdate event of the Program_ID combo box. In it, you'll want to have a line that says
RecordNumber=DMax("fldRecordNumber","tblRecord","[fldProgramID] = " & Program_Input)+1

This way, when a program ID is selected from the Program_ID combo box, the value of the RecordNumber field automatically.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[RottPaws]

Apparently, I'm having a grammar problem this morning.

That should say the help files will tell you what the AfterUpdate Event IS, how to use it . . .

And ...when a program ID is selected from the Program_ID combo box, the value of the RecordNumber field will be set automatically.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[rprieto]

Almost there...
Still unable to pass ProgramID to DMax statement. Can I email a test file to you? Only 265K.
Problem lies in DMax statement. It works if I force Me.cboProgramID to be value I am looking for.

Private Sub cboProgramID_AfterUpdate()
' First Insert a New Record
DoCmd.GoToRecord , , acNewRec
' User selects Combo Box Program ID from list
Me.txtProgramID.Value = Me.cboProgramID
' FIX ME - Find highest Hazard # and add ONE to it. This does NOT work YET.
Me.txtHazardNumber = DMax("[fldHazardNumber]", "tblData", [fldProgramID] = Me.cboProgramID) + 1

End Sub

Thanks again.

 

[RottPaws]

You shouldn't need the GoToRecord command because you want this to happen only on new records, so it should be there anyway. Speaking of this, I'd add an event procedure to the form's OnCurrent event to lock the fields if it's not a new record so they can't be changed for existing records in the database.

Anyway...
Think of the DLookup, DMax, and DMin functions as query shortcuts. Basically, you just need to provide three strings containing what field to retrieve, the table from which to retrieve it, and the conditions for the WHERE clause.

You need to surround the static part of your 3rd parameter with quotes and concatenate the variable with an ampersand (&).
Me.txtHazardNumber = DMax("[fldHazardNumber]", "tblData", "[fldProgramID] = " & Me.cboProgramID) + 1





_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!