Data edits through form

[markedback]

I have a form I use to monitor the entries in a table. The form is based on a simple select query which based on just one table. The table has fields like salespersonID, salespersonName, custAcctNum, custName, orderID, date, time.

Whenever their is a blank cell, I use dlookup through a button on the form to
populate the corresponding data. For example if the salespersonName is missing from a record, I use a dlookup on another table with a list of salespersons to populate the salesperson's name by his/her ID.

---------------------------------------
Private Sub salesperson_name_Click()
On Error GoTo Err_salesperson_name_Click

Dim strsalespersonID As String
Dim strsalespersonName As String


If Forms!OrderCheck!salespersonName = "" Then
strsalespersonName = Nz(DLookup("[salespersonName]", "salespersonList", "[salespersonID] = '" & Me![salespersonID] & "'"))
Me.salespersonName = strsalespersonName
End If

Exit_salesperson_name_Click:
Exit Sub

Err_salesperson_name_Click:
MsgBox Err.Description
Resume Exit_salesperson_name_Click
End Sub
----------------------------------------------------------

The button worked initially but then stopped working.

I am confused as to why this is happening. Could this be happening because the fields on the form are 'bound' to the query and this prevents data editing?

Please suggest a solution.

Thanks
Mark.

 

[dhookom]

I don't agree with saving the name and ID of the salesperson in the table. You should only need to store the salespersonID. You can make the salespersonID text box into a combo box that displays the ID and/or the name.

However, the issue with your code might be in assuming that Null is the same as "". They are not the same.

Private Sub salesperson_name_Click()
On Error GoTo Err_salesperson_name_Click

Dim strsalespersonID As String
Dim strsalespersonName As String


If Len(Me!salespersonName & "") = 0 Then
strsalespersonName = Nz(DLookup("[salespersonName]", "salespersonList", "[salespersonID] = '" & Me![salespersonID] & "'"))
Me.salespersonName = strsalespersonName 
End If

Exit_salesperson_name_Click:
Exit Sub

Err_salesperson_name_Click:
MsgBox Err.Description
Resume Exit_salesperson_name_Click
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[markedback]

Duane,

Thanks for your input.

Unfortunately, I am not the DB administrator and have no influence on the structure of the table! I can only query it and correct the discrepancies. I was trying to help the process by building a form that let's me track missing data items.

Also, the change you suggested in the code hasn't change the situation.

Please suggest a alternate solution.

Thanks again,
Mark.

 

[dhookom]

Define "stopped working".

Have you tried placing a some debugging into your code?

Duane
Hook'D on Access
MS Access MVP

 

[markedback]

Duane,

When I first created the form and the button, the button worked as intended. Whenever a record with no salespersonName showed up and I clicked on the button, the salespersonName textbox was populated and the underlying table was updated.

This process worked the first few times but then stopped i.e. clicking on the button stopped populating the salespersonName textbox.

I haven't been able to identify the cause.

Thanks,
Mark.

 

[missinglinq]

Is data imported from elsewhere and added to the form, or is everything entered manually? I ask because, as Duane has said, Null and "" are not the same, and if nothing has been entered into the salespersonName textbox, or a name has been entered and then deleted in any way, the textbox is Null and your code, as presented here, should never have worked.

Of course, if the salespersonID entered in the form doesn't appear in the underlying table, the code will alos "do nothing."

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[dhookom]

Mark,
I just posted faq705-7148. You should consider using one or more of the options to find out what is going on inside your code.

Duane
Hook'D on Access
MS Access MVP

 

[markedback]

missinglinq,

The table (salespersonList) used to pull the data (sales person's name) is in the same database. It is basically a list of all sales people. It contains their Employee number and Name (first and last names). This is list is only changed when some employee leaves or joins the organization.

Like I mentioned earlier, the code worked initially. It looked for the name corresponding the ID (employee number) and populated the name textbox. The recordset type property for the query has been set at 'Dynaset (Inconsistent Updates)' to allow updates to the query.

The purpose of my form is to go back and complete the data in the main table "salesOrder" which conatins the fields - salespersonID, salespersonName, custAcctNum, custName, orderID, date, time. This table is the underlying table for the query which is used in the monitoring form. The sales people sometimes do not enter their names in the 'salesorder' table as their IDs are already entered and go on to fill the other information.


Duane,

I added the debug code the main code but it doesn't generate any error messages.


Thanks,
Mark.

 

[dhookom]

What type of trouble-shooting did you do? Did your code actually run? Were the values as expected? Did you step through the code reviewing the values?

Do I need to make the FAQ more understandable? I never know how much experience readers have with VBA.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

I can debug this in three steps.

1) Check to see if you code is actually firing.
outside the if check put
msgbox "code firing"
if not check "[Event Procedure]" in the event property
2) check to see if your if check works.
Inside the if check
msgbox "in if"
I doubt it works you need
what was suggested
If Len(Me!salespersonName & "") = 0 Then
3)Check to see if your dlookup works
msgbox strsalespersonName