Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
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?

Reply With Quote
  #2 (permalink)  
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
Reply With Quote
  #3 (permalink)  
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.

Reply With Quote
  #4 (permalink)  
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
Reply With Quote
  #5 (permalink)  
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?

Reply With Quote
  #6 (permalink)  
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

Reply With Quote
  #7 (permalink)  
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
Reply With Quote
  #8 (permalink)  
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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:27 AM.


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