Unbound Form (1 Viewer)

[Fahad987]

Hi,

Last month I posted a query on this forum. It solved my problem to the extent that the form being used was a bound form. I am now trying to make it work with an unbound form in my specific scenario. The relevant code is posted here as well.

Private Sub cboToLoc_AfterUpdate()

Dim lngDispID As Long
Dim lngNewDispID As Variant
If Me.cboToLoc.ListIndex <> -1 Then
      
    lngDispID = Nz(DMax("DispatchID", "tblDispatches"), 0) + 1
    lngNewDispID = Nz(DMax("DispatchID", _
                    "tblDispatches", _
                    "cboToLoc = " & Me.cboToLoc & " And WorkDate = #" & Format$(Me!WrkDate, "mm/dd/yyyy") & "#"), lngDispID)
    Me![DN_ID] = lngNewDispID
Else
    Me![cboToLoc] = Null
    Me![DN_ID] = Null
End If

End Sub

The way it should work is to keep the current Dispatch ID same as long as selection in 'ToLoc' combo box is the same. But a new Dispatch ID to be generated as soon as a new selection is made in the 'ToLoc' combo box. However, I am getting error at "cboToLoc". How should this problem be rectified? Regards

 

[Fahad987]

error message pic attached

 

[arnelgp]

lngNewDispID = Nz(DMax("DispatchID", _
"tblDispatches", _
"cboToLoc = '" & Me.cboToLoc & "' And WorkDate = #" & Format$(Me!WrkDate, "mm/dd/yyyy") & "#"), lngDispID)

 

[Fahad987]

lngNewDispID = Nz(DMax("DispatchID", _
"tblDispatches", _
"cboToLoc = '" & Me.cboToLoc & "' And WorkDate = #" & Format$(Me!WrkDate, "mm/dd/yyyy") & "#"), lngDispID)

It worked. Really appreciate your guidance arnelgp

 

[Fahad987]

hi again.
as i figured out, i dont need the work date criteria anymore. i just want to keep current dispatch id as long as the same selection is being made in the "cboToLoc" combo box. The Dispatch ID gets incremented only when a new selection is made in the said combo box. For this, i deleted everything from the ampersand sign after the me.cboToLoc up till the "#" sign. but now it gives me run time error 3075: Syntax error in query expression 'cboToLoc = 'selected text in combo box'. please guide me.

 

[Pat Hartman]

If you normalize your tables, this doesn't require code when using a bound main form with a subform.

 

[Fahad987]

Respected Pat Hartman,
My tables are normalized. I dont need form-subform setup as per my specific need, otherwise i would have gone that way. An unbound form serves my purpose better than a bound one. Kindly guide me here sir.

 

[Pat Hartman]

If you have to duplicate data in multiple rows, you need a parent table to hold the common fields.