Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 25th, 2007, 01:49 PM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default Open Form, run query, open form

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?

 
Old April 26th, 2007, 10:10 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 26th, 2007, 10:24 AM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old April 26th, 2007, 10:27 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 26th, 2007, 10:37 AM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old April 26th, 2007, 10:59 AM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old April 26th, 2007, 11:04 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old April 26th, 2007, 11:32 AM
Authorized User
 
Join Date: Apr 2007
Location: Fort Bliss, Texas, USA.
Posts: 65
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Open one form or the other mean34dean Access VBA 4 August 21st, 2008 12:02 PM
Not Open Form when no data in query mean34dean Access 4 August 21st, 2008 11:46 AM
Close all MdiChield form from open one form/Button salman .NET Framework 2.0 6 December 10th, 2007 03:21 AM
Close all MdiChield form from open one form salman .NET Framework 1.x 0 November 8th, 2007 12:32 AM
Determine if a form is open Danton Access VBA 1 October 7th, 2004 04:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.