URGENT!! How to open specific record but must FULFILL 3 CRITERIA!!

[liwan]

Dear PPL,
I am really desperate to get this database working. I have Form A which have many textboxes each for specific time E.G : [time1]7.00,[time2] 7.30,[time3]8.00. Form A also have an activeX calendar and also txtbox1 for name.
Form B will have many textboxes for many field but only 3 needed for specification of criteria. TxtName for name ,TxtTime for Time and TxtDate for Date.
I want the textboxes for time be able to open FormB when dblclick but b4 that FormB must fulfill 3 criteria.

1:Form B must have the same time on TxtTime matching the time in Form A E.g: [time1]=[TxtTime]
2:[TxtDate]in Form B must match the activeX inForm A
3: [TxtName] must match the name inForm A [txtbox1]

Please help anybody? Really urgently needed!!THanks
Regards,
Amanda

 

[roltrap]

Hi Amanda,

I don't see whats the problem. When you douleclick in the textbox for Time, can't you just check whether the criteria are fulfilled? If so continue. if not, display a messagebox.
But you probably mean something else?

regards Roltrap

 

[liwan]

Dear Roltrap,
The problem is the criteria doesnt work at all. When dblclick it open Form B directly without fulfilling the criteria. I used link criteria,but only 1 criteria can be use at a time and not all 3 can be use. Wat codes should I be putting in , in order to get it go through the 3 criteria b4 opening it? Please help , urgent! Gonna be fired soon!Thanks

Regards,
amanda

 

[Eupher]

Amanda, maybe you could clarify what you are trying to do. Are you saying that when the user double-clicks on a date field in formA that formB should open, filtered to the correct record based on 3 criteria? Or that formB should open and have those three values automatically written into the appropriate fields? Is formB bound to a table? Does that table have a primary key (an autonumber field, for instance)? Does formB have a field that holds the primary key value? I'm guessing you want the formB filtered to the correct record. I'm also going to assume that formB is bound to a table, which I'll call "tblMyTable" and tblMyTable has an autonumber primary key, which I'll call "MyID". And I'm going to assume the field names you've given for formB match the field names for the underlying table. Also, you don't mention the name of your ActiveX calendar control, so I'll name it "calActiveX". My example also assumes there would only ever be one record that would match all 3 criteria. Here's how I would approach it:

In the On Dbl Click event of each time textbox on formA, create an event procedure that looks something like this:

Private Sub Time1_DblClick(Cancel As Integer)
Dim CurDB as Database
Dim Rs as Recordset
Dim SQLStmt as String
Dim lngRecID as Long

Set CurDB = CurrentDb()
SQLStmt = "SELECT MyID, TxtTime, TxtDate, TxtName " _
& "FROM tblMyTable " _
& "WHERE ((TxtTime = '" & Me!time1 & "') AND " _
& "(TxtDate = '" & Me!calActiveX & "') AND " _
& "(TxtName = '" & Me!txtbox1 & "'));"

Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)
lngRecID = Rs!MyID
Rs.Close

DoCmd.OpenForm "formB", , , "MyID = '" & lngRecID & "'"

End Sub

Obviously I've made some guesses as to your table and form structure, so you'll need to substitute field names as appropriate. If I'm on the wrong track as to your intent, post again. With some more info we can probably patch together some code to get you going.

Ken S.

 

[willir]

Amanda

Have you tried using either the OpenArgs when openning FormB, or the Where clause feature.

Here is how I would do this...

On the event Double Click in FormA
- Check to make sure the criteria in FormA is met - bullet proof
- Setup the desired search criteria for FormB
-- Filter / select clause to apply. Ken S has given you one method of achieving this
-- Variables used to populate FormB fields

- With the DoCmd.OpenForm, include the OpenArgs and probably the Where clause

In FormB, for the On Load event
- Check for the presence of the OpenArgs
- If the OpenArgs is used to open the form, apply the filter. You can also build an SQL statement and use it.
- Populate the required fields either using the OpenArgs or the referencing FormA

I would prefer to pass everything that is required from FormA to FormB, but there are a couple of work-arounds that may be useful if you can't get it to work...
- You can reference the fields in FormA from FormB to grab your information. You can use the expression builder to accomplish this, or what ever.
- You can use hidden / invisble fields in FormA to store your data.

Richard

 

[liwan]

Dear Ken,
WHat you define are partial right. Ihave 2 forms ( frmTask & DiaryPlannerfrm)which have bounded table( Task Folders & DiaryPlannertbl)
Primary key for Task Folders is TaskID
Part ONE
In form DiaryPlannerfrm I have textboxes for time which have properties name as follow0700,0730,0800,0830,0900
etc .
In this form,there are textbox for name[Text89] but the table bound to this field name is Emp Name.
There are also Subform where there is an ActiveX called ActiveXCtl343.
Part Two
In form frmTask, there are many fields.All we need is 3 field in this form in order to get specific record,for read-only.
They are Due Date,Time Start & Surveyor
User will select a date from ActiveXCtl343 in subform and also a drop down list [Text89] to select name.
Then the user will dblclick on textbox on specific time that they want E.g: 0700
When dblclick I want it to run the filter and find the specific record in Task Folders(tbl for frmTask)if they found the SPECIFIC record then open frmTask to show the record. If not then "record not found".
Basically, if Due Date = ActiveXCtl343 and
if Time Start = 0700 and
if Surveyor = Text89 then OPEN frmTask
If there is any more question feel free to ask.

Regards,
Amanda

 

[liwan]

Dear Willir,
Another thing is, SQL doesnt seems to work well on this damn database.I tried this but there is compile error message appear.What's wrong with this?

Private Sub Ctl0800_DblClick(Cancel As Integer)
On Error GoTo Err_Ctl0800_DblClick

Dim CurDB As Database
Dim Rs As Recordset
Dim SQLStmt As String
Dim lngRecID As Long

Set CurDB = CurrentDb()
SQLStmt = "SELECT Task_ID, Due_Date, Surveyor, Time_Start " _
& "FROM Task_Folders " _
& "WHERE ((Time_Start = '" & Me![0800] & "') AND " _
& "(Due_Date = '" & Me!ActiveXCtl343& "') AND " _
& "(Surveyor = '" & Me!Emp_Name & "'));"

Set Rs = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)
lngRecID = Rs!MyID
Rs.Close
DoCmd.OpenForm "frmTask", , , "Task_ID = '" & lngRecID & "'"
Exit_Ctl0800_DblClick:
Exit Sub

Err_Ctl0800_DblClick:
MsgBox Err.Description
Resume Exit_Ctl0800_DblClick
End Sub

 

[liwan]

Dear PPL,
What is wrong with this SQL statement? THere is error say expected end of statement.This whole SQL is red in color.When run , error says compile error!!

SQLStmt = "SELECT Task_ID, Due_Date, Surveyor, Time_Start " _
& "FROM Task_Folders " _
& "WHERE ((Time_Start = '" & Me![0800] & "')AND " _
& "(Due_Date = '" & Me!ActiveXCtl343& "') AND " _
& "(Surveyor = '" & Me!Emp_Name & "'));"

How to fix this?Please help!!
Regards,
Amanda

 

[liwan]

Dear Willir,
Thanks for your time but Me have no idea what are you talking about.Me not good in programming. Can you modify from the code that I send? DOes it make any sense to you?I checkout OpenArgs,isn't it more like a specific lookup? There is error on that FROM clause .The table for frmTask is Task Folders.So wat is wrong with this?

Private Sub Ctl0800_DblClick(Cancel As Integer)
On Error GoTo Err_Ctl0800_DblClick

Dim CurDB As Database
Dim RS As Recordset
Dim SQLStmt As String
Dim lngRecID As Long

Set CurDB = CurrentDb()
SQLStmt = "SELECT Task_ID, Due_Date, Surveyor, Time_Start " _
& "FROM Task_Folders" _
& "WHERE ((Time_Start = '" & Forms!DiaryPlannerfrm![0800] & "')AND " _
& "(Surveyor = '" & Forms!DiaryPlannerfrm![Emp Name] & "')AND " _
& "(Due_Date = '" & Forms!DiaryPlannerfrm![ActiveXCtl343] & "'));"

Set RS = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)
lngRecID = RS!TaskID
RS.Close
DoCmd.OpenForm "frmTask", , , "Task_ID = '" & lngRecID & "'"
Exit_Ctl0800_DblClick:
Exit Sub
Err_Ctl0800_DblClick:
MsgBox Err.Description
Resume Exit_Ctl0800_DblClick
End Sub

 

[Eupher]

Amanda,
I note from one of your previous posts that the ActiveX control is on a subform of the DiaryPlannerfrm. For this to work, you'll need to include the name of the subform in your reference in the SQL statement:

& "(Due_Date = '" & Forms!DiaryPlannerfrm!name_of_your_subform!ActiveXCtl343 & "'));"

I see that you found the error in your SQL statement - the misplaced & character immediately following your ActiveX control reference. I note also that you've gone to the long-hand version of referring to the fields on your form. That's fine, although Me! in place of Forms!DiaryPlannerform! should work equally well.

Ken S.

 

[Eupher]

Amanda,
I forgot in my previous post that you wanted to test for an existing record, so I put an If...Then statement in your code. Note also that you'll have to replace "name_of_your_subform" with the actual name of your subform for the ActiveX field reference in your SQL statement to work. And I added a few missing spaces in your SQL statement that would have cause the string to concatenate strangely (might have worked anyway, but why take the chance). Here's your code:

Private Sub Ctl0800_DblClick(Cancel As Integer)
On Error GoTo Err_Ctl0800_DblClick

Dim CurDB As Database
Dim RS As Recordset
Dim SQLStmt As String
Dim lngRecID As Long

Set CurDB = CurrentDb()
SQLStmt = "SELECT Task_ID, Due_Date, Surveyor, Time_Start " _
& "FROM Task_Folders " _
& "WHERE ((Time_Start = '" & Forms!DiaryPlannerfrm![0800] & "') AND " _
& "(Surveyor = '" & Forms!DiaryPlannerfrm![Emp Name] & "') AND " _
& "(Due_Date = '" & Forms!DiaryPlannerfrm!name_of_your_subform![ActiveXCtl343] & "'));"

Set RS = CurDB.OpenRecordset(SQLStmt, dbOpenDynaset)
If RS.RecordCount = 0 Then
     MsgBox "Record not found."
     Rs.Close
     Exit Sub
     Else
          lngRecID = RS!TaskID
          RS.Close
          DoCmd.OpenForm "frmTask", , , "Task_ID = '" & lngRecID & "'"
End If
Exit_Ctl0800_DblClick:
    Exit Sub
Err_Ctl0800_DblClick:
    MsgBox Err.Description
    Resume Exit_Ctl0800_DblClick
End Sub

Ken S.

 

[liwan]

Dear Ken,
I tried Me! but then the whole 4lines of statement turn red,that is why I change to the long-winded one.Another error pops up saying Microsoft Jet couldnt find input table or query'Task Folders.make sure it exist and check spelling.SQL just dont works well in this db.
I am so sure of my table name is Task Folders and the form name is frmTask.
ANother question, I have a form that have 2 fields,Comp Name & Contact Name. I want to be able to select the Comp Name on a dropdown list.(Which I have a query on Comp Name)Then I want the second field (Contact Name) to list only the contact name from that company in a dropdown list as well.
Meaning ,user will select the comp name then according to the comp name, user will click on drop down list to select the contact name for that comp name ONLY.

But more urgently, on the 3 criteria. It still doesnt work. I don't mind sending the database over if you want but is quite huge 26MB.

REGards,
Amanda

 

[Eupher]

What's the name of your table? I see it "Task_Folders" in your code, but "Task Folders" in your message. Which is it?

Ken S.

 

[liwan]

Dear Ken,
My table name is Task Folders. But when using it in VBA I need to put as Task_Folders to hold it together if I don't put _ then it will read as table Task instead of Task Folders. But somehow neither of it work. THe same error message came up...saying can't find the table or query that name?? I wonder why. I check the spelling and everything else,still can't work.

Regards,
Amanda

 

[Eupher]

No, that's not correct. If the name of the object, in this case your table, contains spaces (or certain other non-alphanumeric characters) you must enclose the name in square brackets. Some people advocate ALWAYS enclosing object names in brackets, just in case. [Task Folders] is the correct way to refer to your table.

Ken S.

 

[liwan]

Dear Ken,
I have also tried that way u said....by using the bracket. I did tried using [Task folders] but still it doesnt work as well. What is the problem? when I use bracket in the Task folders, there is another error message saying lack of 3 more bracket , when add all the bracket then another error msg...
The point is..SQL never seem to work...How do I fix this?
Regards,
Amanda

 

[DougP]

Amanda,
Try creating the SQL in a Query using the QBE grid
This way you can use the "Build" option to get the forms coorect syntax. If you right click in the criteria box of any field you will see the Build... option this will then list everything in your database that you can use. Click forms and drill down to the textbox click OK and it will put the correct syntax in the Criteria.
when it runs in there then click the SQL view in the Query and copy it to your VBA code.
You will have to make tiny adjustment but at least you can let Access design the SQL code for you.
Where ever Access put a Double Quote you have to change this to a single quote when you use it in VBA


DougP, MCP

 

[liwan]

Dear Doug,
I tried whatyou said b4 as well.But anything I change from dblquote to single quote will be highlighted red (ERROR). This form is base on query already.SQL just don't work!!!What is wrong in the code above? How do I modify so that it will be workable?
Please help anybody...
REgards,
Amanda