DoCmd.FindRecord

[hitpoint]

So, I'm trying to figure this one out. If a record exists, I want it to recall the existing record and have it populate the field. If it doesn't, I want it to automatically write those fields to the new table.

Private Sub LP__BeforeUpdate(Cancel As Integer)
Dim db As Database, tb As Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("Select * from [LP Table] where LPNUM =" & Me!LOADPLANNUM)
If Not tb.EOF Then
MsgBox ("This record already exists. Press 'OK' to load existing Master.")
DoCmd.FindRecord Me!LOADPLANNUM
End If
If tb.EOF Then
Me!SSLBKNGNUM = tb!BKGNUM
Me!VSL = tb!VSL
Me![# PKGS] = "1X" & tb!SIZE]
Me![ULTIMATE DESTINATION] = tb![DEST]
Me![MARKS AND NO'S] = "IN CNTR NO: " & tb![CNTRNUM] & Chr(13) & Chr(10) & "SN:" & tb![SEAL#]
Me![# PKGS] = "1X" & tb![SIZE]
Me![PORT OF UNLOADING] = tb![DEST]
Me![PLACE OF DELIVERY] = tb![DEST]
End If
tb.Close
Set db = Nothing
End Sub

 

[ajetrumpet]

so what is happening with the code? If I remember correctly, docmd.findrecord doesn't do anything if there is no record, but I could be wrong.

 

[wazz]

Dim rs2 As DAO.Recordset
Set rs2 = Me.Recordset

If Not tb.EOF Then
    MsgBox ("This record already exists. Press 'OK' to load existing Master.")
    rs2.findfirst "LPNUM =" & Me!LOADPLANNUM
else
...

 

[georgedwilkinson]

Just a drive by here...not trying to hijack the thread:
hitpoint, you have object names with special characters and spaces. That can cause all kinds of problems (especially # and ', which both have meaning in Access SQL). You might want to correct that while the project is young.

Now, back to your regularly scheduled helpers.

 

[hitpoint]

Thank you all, so so much. Everything works beautifully.

And George, yes, I desperately need to clean up my code. This was my first take on an Access DB, and some of my original forms are still infantile.

 

[georgedwilkinson]

Glad Adam and Wazz got it working for you. You should clean this up sooner rather than later...it'll cause problems down the road.

 

[hitpoint]

Oof. Shamefully, I spoke too soon. The code doesn't want to work. It hilights the "rs2.FindFirst "LPNUM =" & Me!LOADPLANNUM" line as the error line and gives me, "The action was cancelled by an associated object." Here's the full block of code, just for kicks:

Private Sub LP__BeforeUpdate(Cancel As Integer)
Dim db As Database, tb As Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("Select * from [LP Table] where LPNUM =" & Me!LOADPLANNUM)
Dim rs2 As DAO.Recordset
Set rs2 = Me.Recordset
If Not tb.EOF Then
    MsgBox ("This record already exists. Press 'OK' to load existing Master.")
    rs2.FindFirst "LPNUM =" & Me!LOADPLANNUM
    End If
    If Not tb.EOF Then
Me!SSLBKNGNUM = tb!BKGNUM
Me!VSL = tb!VSL
Me![# PKGS] = "1X" & tb!SIZE
Me![DEI HBL#] = tb![1HBL#] & ", " & tb![2HBL#] & ", " & tb![3HBL#] & ", " & tb![4HBL#]
Me![ULTIMATE DESTINATION] = tb![DEST]
Me![MARKS AND NO'S] = "IN CNTR NO: " & tb![CNTRNUM] & Chr(13) & Chr(10) & "SN:" & tb![SEAL#]
Me![# PKGS] = "1X" & tb![SIZE]
Me![DESCRIPTION OF COMMODITIES] = "STC: " & Chr(13) & Chr(10) & tb![1MAKE] & Chr(13) & Chr(10) & "VIN:" & tb![1VIN] & Chr(13) & Chr(10) & tb![2MAKE] & Chr(13) & Chr(10) & "VIN:" & tb![2VIN] & Chr(13) & Chr(10) & tb![3MAKE] & Chr(13) & Chr(10) & "VIN:" & tb![3VIN] & Chr(13) & Chr(10) & tb![4MAKE] & Chr(13) & Chr(10) & "VIN:" & tb![4VIN]
Me![PORT OF UNLOADING] = tb![DEST]
Me![PLACE OF DELIVERY] = tb![DEST]
End If
tb.Close
Set db = Nothing
End Sub

 

[ajetrumpet]

Now, back to your regularly scheduled helpers.

LOL


Here's a kickback:

Private Sub LP__BeforeUpdate(Cancel As Integer)
Dim db As Database, [COLOR="Red"][B]tb As Recordset[/B][/COLOR]
Set db = CurrentDb
Set [COLOR="Red"][B]tb = db.OpenRecordset[/B][/COLOR]("Select * from [LP Table] where [COLOR="Red"][B]LPNUM =" & Me!LOADPLANNUM)[/B][/COLOR]
Dim rs2 As DAO.Recordset
[COLOR="Red"][B]Set rs2 = Me.Recordset[/B][/COLOR]
If Not tb.EOF Then
    MsgBox ("This record already exists. Press 'OK' to load existing Master.")
    [COLOR="Red"][B]rs2[/B][/COLOR].FindFirst [B][COLOR="Red"]"LPNUM =" & Me!LOADPLANNUM[/COLOR][/B]
    End If

 

[wazz]

If Not tb.EOF Then
    MsgBox ("This record already exists. Press 'OK' to load existing Master.")
    [COLOR="Red"]Cancel = True[/COLOR]
    rs2.FindFirst "LPNUM =" & Me!LOADPLANNUM
Else
    Me!SSLBKNGNUM = tb!BKGNUM

 

[hitpoint]

So the bugger is still being weird. I tried a bunch of uninformed variations, like adding an "End If" and deleting the "Else", etc, but nothing seems to want to make the code work. With this version below, I still get the "This action was cancelled by an associated object."

Private Sub LP__BeforeUpdate(Cancel As Integer)
Dim db As Database, tb As Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("Select * from [LP Table] where LPNUM =" & Me!LOADPLANNUM)
Dim rs2 As DAO.Recordset
Set rs2 = Me.Recordset
If Not tb.EOF Then
    MsgBox ("This record already exists. Press 'OK' to load existing Master.")
    Cancel = True
    rs2.FindFirst "LPNUM = " & Me!LOADPLANNUM
Else
Me!SSLBKNGNUM = tb!BKGNUM
Me!VSL = tb!VSL
Me![# PKGS] = "1X" & tb!SIZE
Me![DEI HBL#] = tb![1HBL#] & ", " & tb![2HBL#] & ", " & tb![3HBL#] & ", " & tb![4HBL#]
Me![ULTIMATE DESTINATION] = tb![DEST]
Me![MARKS AND NO'S] = "IN CNTR NO: " & tb![CNTRNUM] & Chr(13) & Chr(10) & "SN:" & tb![SEAL#]
Me![# PKGS] = "1X" & tb![SIZE]
Me![DESCRIPTION OF COMMODITIES] = "STC: " & Chr(13) & Chr(10) & tb![1MAKE] & Chr(13) & Chr(10) & "VIN:" & tb![1VIN] & Chr(13) & Chr(10) & tb![2MAKE] & Chr(13) & Chr(10) & "VIN:" & tb![2VIN] & Chr(13) & Chr(10) & tb![3MAKE] & Chr(13) & Chr(10) & "VIN:" & tb![3VIN] & Chr(13) & Chr(10) & tb![4MAKE] & Chr(13) & Chr(10) & "VIN:" & tb![4VIN]
Me![PORT OF UNLOADING] = tb![DEST]
Me![PLACE OF DELIVERY] = tb![DEST]
End If
tb.Close
Set db = Nothing
End Sub

 

[wazz]

i'm still trying to figure out what aj was getting at ...

but i want to check what ur doing. it looks like you're filling out an entire form and then checking to see if an LPNUM exists. is that right? how about checking the LPNUM first, say in a combobox at the top of the form. if it's on the list, select it, if not, fill out the form.

 

[hitpoint]

Pretty much. This operation should be a failsafe during editing. The user inserts the reference number, and if the record exists, it can be edited in its old form, and if it's new, it can be populated as much as possible with existing information elsewhere, and then have some more info added to it.

That wouldn't work, simply because of how many records I expect in this database. About four or five are added every day, and it's impractical to scroll down and select one through a combobox.

 

[wazz]

you don't have to scroll in a combobox, you just start typing, if it's there, it automatically scrolls to it (auto expand).

---

force user(s) to start with LPNUM and do your check on the beforeupdate of that field, instead of the whole form. use a dlookup to see if it exists.

if it's new, it can be populated as much as possible with existing information elsewhere

the code you have now is looking for an existing record *already in the form's recordset*. when you say "load existing master" what do you mean? where's the data coming from?