Ok, this is the situation.

I have a form, 'frmOpenTask', which has a cmd button on it, 'cmdFindTask'. The user enters a Task ID (known number) into an unbound text box and then when they click the 'Find!' button (cmdFindTask) it runs a query to find that task and opens the form, 'frmTask' with that record and all the data in it. This then allows the user to update the task, print and or save the form/record with the updated information.

Anyone? Anyone?

Create a combo box that looks up the task number, tied to the PK of the task table. Call it cboTask

Make sure this number is on your form you want to open.

Then put a button on the first form, call it btnTask

Then put this on the on click event of btnTask

Dim iTask As Integer
Dim sForm, sLink As String

If IsNull(Me.cboTask) or Me.cboTask = "" Then
   MsgBox "Please select a task number.", vbCritical
   End Sub
Else
   iTask = Me.cboTask
End If

sForm = "frmTask"
sLink = "[TaskID] = " & iTask

DoCmd.OpenForm sForm, acNormal, , sLink

'Make sure you put sLink in the WHERE clause of DoCmd, I forget which comma it comes after.

Did that help?



mmcdonal

mmcdonal,

I appreciate the suggestion and will try that.

However, I don't know if my explaination was correct. I am trying to allow people to input a known Task_ID number into the unbound text box and click 'Find!', the db then opens the Task in the frmTask for being edited.

I think that putting a combo box, like you suggested, where the user pulls down all of the entries in the Task_ID field will be a little awkward for the user, to say the least. I am not trying to display all of the Task IDs to the person, just open the one that they need.

I apologize if I sound rude in this post, I am not trying to. I appreciate the help though.

Dim iTask As Integer
Dim sForm, sLink As String

If IsNull(Me.txtTask) or Me.txtTask = "" Then
   MsgBox "Please select a task number.", vbCritical
   End Sub
Else
   iTask = Me.txtTask
End If

sForm = "frmTask"
sLink = "[TaskID] = " & iTask

DoCmd.OpenForm sForm, acNormal, , sLink


mmcdonal

Ok, I tried that code. It seemed like it would work. The 'unbound' text box area on the form was labeled 'Task' to comply with the code.


On the line "If IsNull(Me.txtTask) or Me.txtTask ="" Then .... When I run it in the code window I get this message 'Compile error: method or data member not found', where 'txtTask' is highlighted in (Me.txtTask)

Any clue?

Wonder if this would work? I got it from my friends old, corrupted db where he did the same thing that I'm trying:


Dim strID As String, bValid As Boolean
Dim rsRecordset As Recordset

'check for valid record id

txtLogID.SetFocus
strID = txtLogID.Text
bValid = True

If Not IsNumeric(strID) Then
    bValid = False
Else
    If Val(strID) <> Int(Val(strID)) Then
        bValid = False
    End If

    Set rsRecordset = CurrentDb.OpenRecordset("SELECT * FROM DOCUMENTS WHERE Documents.DocID=" & strID, dbOpenDynaset, dbReadOnly)
    If rsRecordset.RecordCount <> 1 Then 'ambiguous record/ does not exist
        bValid = False
        MsgBox "The record number does not exist.", vbInformation, "Invalid Record"
    End If
    Set rsRecordset = Nothing
End If

If Not bValid Then Exit Sub

DoCmd.OpenForm "frmDocumentLog", acNormal, , , acFormEdit, acDialog, strID
DoCmd.Close acForm, Me.Name, acSaveNo

End Sub

Since you are not using a combo box (standard) then you will have to check to make sure the data entered is numeric.

You can check to see if the record exists before you open the form. Forms do not have an On No Data event.

A combo box (with autocomplete functions!) would eliminate the need for all this.

mmcdonal

Mike,

I figure I'm explaining what I'm trying wrong. I emailed you a file, contains my db currently working on, my friends old, corrupted db, three other files. It may be easier to see what I am doing with that. Just look at the readme file first.