Fun and games once again!!

[Sam Summers]

I have been round and round trying to get my head round this seemingly simple problem but still have not quite got there.
All i am trying to do is open an unbound form 'Transfer' the user then selects a location from a combobox 'JobNo' whose Data is set to a query which includes the LocationID and the JobNo. On change of this combobox, the focus is moved to an unbound textbox 'NumberInput' where the user enters the number of the item and this is checked to ensure it exists in the 'Equipment' Table before running an update query which is based on the Combobox 'JobNo'. This query moves the item from the current location to the selected one. Following this, there is a subfom on this 'Transfer' form (TransferLocationSubform) that i then want to Requery to show each item as it is moved.

The problems i cant seem to solve are:-

1) If the user does not enter a number or anything into the 'NumberInput' box this must be prevented.

2) The subform is staying blank even after the item has been moved.

3) I want the user to just type in the number and hit the 'Enter' key when these actions will be carried out but I want the 'NumberInput' box to be cleared ready for the next item to be entered.

Here is the 'Transfer' Form code:-

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)

Me.JobNumber.SetFocus
Forms!ViewbyLocation!JobNo.SetFocus
Me.JobNumber.Text = Forms!ViewbyLocation!JobNo.Text
DoCmd.Close acForm, "ViewbyLocation"

End Sub

Private Sub JobNo_Change()

Me.NumberInput.SetFocus

End Sub

Private Sub NumberInput_AfterUpdate()

Me.Refresh
Forms!Transfer!TransferLocationSubform.Requery
Me.NumberInput.SetFocus
'Me.NumberInput.Text = "" '(This line doesnt work?)

End Sub

Private Sub NumberInput_BeforeUpdate(Cancel As Integer)

Dim dbs As Database, rst As Recordset
Dim strCriteria As String
Dim stDocName As String

If Me.NumberInput.Text = "" Or IsNull(NumberInput) Then MsgBox "incorrect number", vbOKOnly, "EquiTrac" Else
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Equipment", dbOpenDynaset)
strCriteria = "[ItemNo]='" & Me.NumberInput & "'"
rst.FindFirst strCriteria
If rst.NoMatch Then
MsgBox "incorrect entry", vbOKOnly, "EquiTrac"
Else
stDocName = "TransferQry"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Do Until rst.NoMatch
rst.FindNext strCriteria
Loop
End If

End Sub

***********************************************

Here is the query for the subform:-

SELECT Equipment.EquipmentID, EquipLookup.EquipDescription, Location.JobNo, Equipment.LocationID, Equipment.EquipRef, Equipment.ItemNo, Equipment.TestDate, Equipment.Scrapped, Equipment.Lost, Equipment.HireDate
FROM Location INNER JOIN (EquipLookup INNER JOIN Equipment ON (EquipLookup.EquipRef = Equipment.EquipRef) AND (EquipLookup.EquipRef = Equipment.EquipRef)) ON (Location.LocationID = Equipment.LocationID) AND (Location.LocationID = Equipment.LocationID)
WHERE (((Location.JobNo)=[Forms]![Transfer]![JobNo].[Text]) AND ((Equipment.Scrapped)=False) AND ((Equipment.Lost)=False))
ORDER BY EquipLookup.EquipDescription, Equipment.ItemNo;

************************************

If you can help it would be fantastic.

Thank you very much.

 

[RichO]

I may not have all your answers but maybe some help. In your code,

If Me.NumberInput.Text = "" Or IsNull(NumberInput) Then MsgBox "incorrect number", vbOKOnly, "EquiTrac"

I would change this to:

If Nz(Me.NumberInput) = "" Then 
     MsgBox "incorrect number", vbOKOnly, "EquiTrac"
     Cancel = True
Else

Nz will convert a null to an empty string, and if the input is blank you want to cancel the update.

You also have:

'Me.NumberInput.Text = "" '(This line doesnt work?)

Try just plain old Me.NumberInput = ""

And...

strCriteria = "[ItemNo]='" & Me.NumberInput & "'"

If ItemNo is a numeric field you don't want the 'quotes' around the NumberInput.

strCriteria = "[ItemNo]=" & Me.NumberInput

Hope I could be of some help...

 

[bjackson]

You could also use
if not isnumeric(me.NumberInput) then