Lookup value and return next in series

[jmbcreative]

Okay. Now bare with me 'cause this is slightly complicated.

I have a subform (HardwareComponents subform) that is based on a query that looks up ControlID's. These ControlID's are in a numeric-alphabetic form like such 142a, 142b, 142c.

I need a input field (controlID) on the main form(HardwareComponents) to look up the values in the subform and return the next consecutive number. Which in the case above would be 142d.

I assume this may requere a SQL statement of sorts. Any Ideas?

 

[Remou]

I guess something along these lines:

Private Sub Form_BeforeInsert(Cancel As Integer)
  Dim strAlpha, strMax
  strAlpha = "abcdefghijklmnopqrstuvwxyz"
  strMax = DMax("ID", "tblTable")
  Me.ID = CStr(Val(strMax)) & Mid(strAlpha, InStr(strAlpha, Right(strMax, 1)) + 1, 1)
End Sub

Though you will probably need a means of identifying the sub set on the subform, for example:
[tt]DMax("ID", "tblTable","MainID=" & Me.MainID)[/tt]

 

[jmbcreative]

Within the DMax statement, what values do I use?

The subform is referencing a query.

 

[Remou]

You need the name of the control to look up, the name of the query and, optionally, a Where clause. At its simplest:
[tt]DMax("ControlID", "Name of Query")[/tt]

Whether or not you need a Where clause depend on how the subform and query are set up.

 

[jmbcreative]

Thanks. I inserted the values and I am recieving a compile error stating "expecting =" at the end of the DMax statement.

 

[jmbcreative]

So it looks like my where statement has to reference the main form to fill the criteria since it will be dependanton the record that is active.

 

[Remou]

I don't think that is the problem here. Can you post the code as modified?

 

[jmbcreative]

Sure thing.

*******

Private Sub Form_Load()
Me.System.Value = Form_ChangeLogEntry.System.Value
DoEvents

Dim strAlpha, strMax
strAlpha = "abcdefghijklmnopqrstuvwxyz"
strMax = DMax("[ControlID]", "HardwareList components Query")
Me.ControlID = CStr(Val(strMax)) & Mid(strAlpha, InStr(strAlpha, Right(strMax, 1)) + 1, 1)

*******

The subform that I need to reference is a child of a system field on the main form. This returns the controlID's for the system selected. So basically I need to reference the controlID field on the subform(after it has been updated) and input the next incrimental value into the controlID field on the main form. Make sense?

 

[PHV]

At least:
strMax = DMax("[ControlID]", "[!][[/!]HardwareList components Query[!]][/!]")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmbcreative]

Thanks PHV! I am at least getting a value to show up now, but it is not accurate. I think the code is referencing the query table, not the subform output, if that makes sense.

Is there a way that I can have the code reference the subform for it's value?

 

[PHV]

Have a look at the 3rd argument of the DMax function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886