Solved Struggling to use Dlookup to find value from input

[DarkBrewer]

Howdy,

I've been struggling with this for a bit and suspect I'm missing something simple or not doing it in an appropriate manner. I have a form that takes user input and it works to update a record if it doesn't exist like I'm expecting. My struggle pertains to checking to see if the value exists, and if it does, add a record to a different field.

Here is the code I'm using:

Option Compare Database
Option Explicit
Private Sub Text0_Enter()

Dim rs As Recordset
Dim strCriteria As String
Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)

On Error GoTo ErrHandler

If IsNull(Me.Text0.Value) Then
MsgBox ("Please enter the Job Number.")
Exit Sub
End If

'area I can't figure out and obviously not structing Dlookup correctly'
If (DLookup("JobName", "Table1", "JobName" = Me.Text0.Value"))Then
rs.AddNew
rs.Fields("TimeOut").Value = Now()
rs.Update

Else

rs.AddNew
rs.Fields("JobName").Value = Me.Text0.Value
rs.Fields("TimeIn").Value = Now()
rs.Update

End If

Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation, "Error Occurred"

End Sub

What would I need to change in the Dlookup to check for the value being provided from the input textbox?

 

[cheekybuddha]

Is JobName a string or numeric field in your table? (You ask to enter a Job Number if it's missing)

If you want to test whether it exists already in Table1 then DCount() might be more appropriate:

' ...
'area I can't figure out and obviously not structing Dlookup correctly'
If (DCount("*", "Table1", "JobName = '" & Me.Text0.Value & "'") > 0) Then    ' Comment this line if JobName is a numeric field
' If (DCount("*", "Table1", "JobName = " & Me.Text0.Value) > 0) Then             ' Uncomment this line if JobName is a numeric field
  rs.AddNew
  rs.Fields("TimeOut").Value = Now()
  rs.Update
Else
  rs.AddNew
  rs.Fields("JobName").Value = Me.Text0.Value
  rs.Fields("TimeIn").Value = Now()
  rs.Update
End If
' ...

 

[cheekybuddha]

BTW, you have a very long-winded way of going about (what I think) you are trying to do!

 

[cheekybuddha]

PS. Welcome to AWF!!

 

[cheekybuddha]

What is the RecordSource of your form?

 

[DarkBrewer]

Hi Cheeky and thanks for the greeting and quick response. I'm VERY new to VBA, in fact the last time I really did anything with VB was in 1999 with VB6. I recall very little of that class. Anyway, JobName is a string. I added the piece to enter a job number if missing because if I double clicked the form without that code, it would give an error about a null value. Maybe the entry to the form is being done in a weird way, but that took care of that. Dcount could very well be the better method of looking for the record. The recordsource of the form is the only table in the database labeled table1.

 

[cheekybuddha]

Can you not just bind Text0 to JobName, and do away with all the code?

 

[theDBguy]

Hi. Welcome to AWF!

Just FYI, modifying the table via code from the form bound to the same table could sometimes lead to a write conflict error.

 

[DarkBrewer]

Can you not just bind Text0 to JobName, and do away with all the code?

Quite possibly. I'll look into doing it in that manner and see if I can accomplish what I'm trying to do. I'll reply once I give that a shot.

 

[DarkBrewer]

I suppose it wouldn't hurt to explain what I'm trying to do to...
I want to provide input from a text box. If the record does not exist, and the new record from the input to the JobName field, and add a timestamp of the current date/time, in a "TimeIn" field. If the record already exists, add the current date/time to the "TimeOut" field.

Attempting Cheeky's suggestion now...

 

[DarkBrewer]

S

Can you not just bind Text0 to JobName, and do away with all the code

 

[DarkBrewer]

I just tried this. While it will take the input I provide, and add a record (if it doesn't exist) to the JobName field, I additionally need to have it add the current date/time to the TimeIn field associated with that record. Also, if it already exists, add the current date/time to the "TimeOut" field. Is there a way to do that with the form without code?

 

[MajP]

You can set the default value of a field to the function
=Now()
This will create a time stamp when a record is created.
Or you can update the record with a time stamp on the form's after update.

 

[cheekybuddha]

Be careful - if you bind the control to JobName, and then type in that field, it will just change the value of the existing record, not create a new record.

 

[cheekybuddha]

I think you might be better off adding a header to your form.

In the header add an unbound textbox called something like txtSearch.

Then add some code to its AfterUpdate event like:

Private Sub txtSearch_AfterUpdate()

  With Me
    If Len(.txtSearch & vbNullstring) > 0 Then
      With .RecordsetClone
        .FindFirst "JobName = '" & .txtSearch & "'"
        If Not .NoMatch Then
          Set Me.Bookmark = .Bookmark
        Else
          DoCmd.GoToRecord , , acNewRec
        End If
      End With
    End If
  End With

End Sub

(NB untested)

 

[DarkBrewer]

I think you might be better off adding a header to your form.

In the header add an unbound textbox called something like txtSearch.

Then add some code to its AfterUpdate event like:

Private Sub txtSearch_AfterUpdate()

  With Me
    If Len(.txtSearch & vbNullstring) > 0 Then
      With .RecordsetClone
        .FindFirst "JobName = '" & .txtSearch & "'"
        If Not .NoMatch Then
          Set Me.Bookmark = .Bookmark
        Else
          DoCmd.GoToRecord , , acNewRec
        End If
      End With
    End If
  End With

End Sub

(NB untested)

Once I set the Record Source for that textbox to Table1 that works perfectly to add a record with the current date/time in the TimeIn Field. Now, if I attempt to add a record that already exists, I get a run-time error 424 (object required) and refers to this line: "Set Me.Bookmark = .Bookmark"

Is there some simple code to change that line so that if the textbox input exists it updates the "TimeOut" field with the current date/time?

I'm tinkering with the method you used to add the record to see if I can do it in that manner.

 

[MajP]

A bookmark is not an object so the SET keyword is not used.
Notice the provided code says "NB Untested" so @cheekybuddha typed it directly into the thread and you need to verify the code.

 Set Me.Bookmark = .Bookmark
to
 me.bookmark = .bookmark

 

[DarkBrewer]

A bookmark is not an object so the SET keyword is not used.
Notice the provided code says "NB Untested" so @cheekybuddha typed it directly into the thread and you need to verify the code.

 Set Me.Bookmark = .Bookmark
to
me.bookmark = .bookmark

I did notice that and that's why I was tinkering with that piece of the code, but I'm very unfamiliar with vba and having to research anything that doesn't make sense one piece at a time. Your suggestion corrected that runtime error, but it now gives a message that states "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship...." So it's acting like it's trying to add a record that already exists instead of updating the "TimeOut" field for the already existing record. It feels like I'm close to achieving what I want, but really struggling with the update to the already existing record. Hopefully, my terminology is not confusing anything.

 

[MajP]

I do not think it is @cheekybuddha code, but your leftover code. Are you still trying to code an "add new" somewhere. The code only searches and moves, does not add anything. Is JobName the PK? If so then your tables are not likely correct. If this tables logs times for a job, then the JobName should be a foreign key to the Jobs table. This table should have a PK which likely is just an autonumber. Can you explain your tables.

 

[DarkBrewer]

I do not think it is @cheekybuddha code, but your leftover code. Are you still trying to code an "add new" somewhere. The code only searches and moves, does not add anything. Is JobName the PK? If so then your tables are not likely correct. If this tables logs times for a job, then the JobName should be a foreign key to the Jobs table. This table should have a PK which likely is just an autonumber. Can you explain your tables.

Perhaps that is part of my underlying issue. My PK is the JobName field. I'll run with your statement that my table is not correct and work on that, but I am also trying to update a different value (TimeOut), if the record exists.