Cheeky Code

[Wazz]

Hi everyone,
I need some help. I have a form, and on that form is a feild that contains a date. My database is built around this date feild. what im after is a peice of code (thats the cheeky bit) that, when I type in the date into the date feild, it brings up all the other feilds (in the same form) that have already been put added to that date.

Example... If some had a table with two feilds one called Date and one called Order.
I would want to type in the date in a form, and it automatically bring up the data within the field 'Order' if a value has been set... I was told to use the Afterupdate event, but not sure what code I need.

Cheers guys

 

[grnzbra]

It seems that the trick would be to set the query's criteria equal to the date field and requery.

 

[Wazz]

as in update a table using a query? could this be done?

 

[GDGarth]

builddate is a field in my database
FilterDate is user-supplied.
DateBox is a combo box on the form where they supply the date.

This code filters the records so only those equal to FilterDate are shown. All you have to do is read your field with the date, assign that to the variable, and apply the filter. The easiest way to do that is to attach the code to the "After Update" event of your text box where they put the date.

Dim FilterDate As Date
Dim DateString As String
Dim FilterString As String
FilterDate=DateBox.Text
DateString = Str(FilterDate)
FilterString="[builddate] = " + "#" + Str(FilterDate) + "#"
DoCmd.ApplyFilter , FilterString

This way, your form's record source can be the table, and you won't have to requery.

 

[Wazz]

Hi,
Sorry im partially with you there. Thats a different way to what I have thought of doing and could work!!

BUT (theres always a but)

Im not sure how the user enters a new date if the date doesnt already exsit. Also is the code you posted above supposed to be used on a combo box? I had a runtime eror when I tried it.

Sorry if im being stupid...

Regards

Wazz

 

[GDGarth]

I use a combo box, because they look up the date from another table. You can use a text box, if that works better (although a combo box can also be used to enter new data).

I don't know what the run time error is. BuildDate is the name of a field in my table. You'd need to change it to your field's name. Maybe somebody else can see where the problem is, but it works in my database.

 

[Wazz]

Thanks for you help GDGarth.

Can anyone else help on that? Is there a way that I can input data as well as retaining its lookup ability in a combo box?

 

[allanon]

Try changing the "+"'s to "&"

 

[GDGarth]

You can add new data in a combo box if you set the "Limit to List" property to "No". That lets you select a value, or type in a new one.

The combo box should be unbound, and located in the header of the form. The form should be bound to your table, with the date, and other fields you want to view, in the detail lines.

 

[Wazz]

So once I have done this... and entered the date in the combo box... Does that code update the table on exit of the combo box?

 

[Wazz]

Ok.. I have tried to do this but im just receiving a runtime error 424. The problem seems to be with the line FilterDate=DateBox.Text

I feel that im so close to what im after... i dont want to give up now!!

cheers

Chris

 

[GDGarth]

Don't put the code in the "ON Exit" event, put it in "After Update". The combo box "DateBox" must have the focus when you try to read it's value, otherwise you'll get an error. If you put it in "On Exit", it will leave, then try to read the value.

Other than that, it should work (it does in my database).

Feel free to email me if you still can't get it to work, and I can send you a sample. It may take me a day or two to get back to you, because I may be away from my computer.

gdgarth@aol.com

 

[Joeclueless]

Hi,

Im trying out GDGarth's DateBox_AfterUpdate code as a personal developmental exercise. Ive sort of got it working. I have a short table I'm using to test out things.

This table has 4 fields:

builddate date/time short date
FilterDate date/time short date (Not Necessary?)
Order text
OrderId AutoNumber

I have the form:
DateBox It has the unbound combobox that shows the FilterDate in the form header.

The fields Order and OrderId are in the details section.

I have the thing to work in order to reference the FilterDate to the builddate. Now I'm trying to have it add a record when NotInList.

The code I typed is not working.

Here's the code:
--------------------------

Option Compare Database

Private Sub DateBox_AfterUpdate()
Dim FilterDate As Date
Dim DateString As String
Dim FilterString As String
FilterDate = DateBox.Text
DateString = Str(FilterDate)
FilterString = "[builddate] = " + "#" + Str(FilterDate) + "#"
DoCmd.ApplyFilter , FilterString


End Sub



Private Sub DateBox_NotInList(NewData As String, Response As Integer)
On Error GoTo DateBox_NotInList_Err
Dim CurConn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim CurDB As Database

Set CurDB = CurrentDb
Set CurConn = New ADODB.Connection

With CurConn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "data source= " & CurDB.Name
.Open
End With

Set rst = New ADODB.Recordset
rst.CursorType = adOpenDynamic
rst.LockType = adLockOptimistic
rst.Open "builddate", CurConn, , , adCmdTable
With rst
' Add new record to end of Recordset object.
.AddNew
![FilterDate] = DateBox.Text ' Add data.
.Update ' Save changes.
End With
rst.Close
DateBox_NotInList_Exit:
Exit Sub
DateBox_NotInList_Err:
MsgBox Err.Description
Resume DateBox_NotInList
End Sub
--------------

Any suggestions?? Thanks again!

Joe

 

[Wazz]

Just to further this on.. could I use the calander control to my advantage?

Coyuld I have it so that when a date is selected in the calander it lets you add data to fields for that date? That way the user wouldnt have to type the date in.. just select it.

 

[Wazz]

Ok.. I have bound the calander with the DATE field in my table. I have then used the code,
If Me.Dirty = True Then
Me.Dirty = False
End If

So that when you click a date in the calander it comes up in the DATE feilds text box.
(See I am trying to work tis out my self!!!!)
but.. How do I get it so that.. I can update the other fields for that date. It doesnt seem to hold the values.

Regards,

Chris

 

[Wazz]

ok...
I have used the above code on my combo box and, it works.. amazing!!!

Next problem... If I enter a new date into the combo box, it doesnt appear in the drop down list until I close the form and reopen it.. Can I add a button for example or a script to autoupdate the combo box list... prefrably as the user leaves the combo box?

Again I have searched for the answer myself, but no luck as yet

Cheers

Wazz

 

[RITec]

I Think that Grnzbra had the easiest idea

Create a query that include the fields you want.

In design view
Click on Criteria field for the date field
Type "[Enter Date]"

When you click on the the query it will ask you for the date and you can edit the fields when it comes up.

Hope this helps

RITec

 

[RITec]

don't type the quotes

just enter [Enter Date]in the criteria field.

 

[Wazz]

Ritec.. I can see (I think) what you mean. It would work if the date was already on the system. Mainly though my database is a data input system.. unless i can enter new details through a query?

Also anyone any idea about input via the calander control otr combo box as per above?

 

[simmsey]

I've just had a quick read through this thread and am slightly confused by the requirement.

If I understand correctly you want to enter a value and then lookup that value to see if already in table, in which case get the other fields for editing OR if not already in existence to go to a new record.
I am assuming that when you lookup the entered value there can be only one match?

It is possible to do these things by using a combination of a combo (unbound) and code to position the record to the matched value or a new record. I think it also needs to be linked to other code that enters a date value if you are on a new record

An Alternate approach:
You might also want to look at using a subform. This has the advantage that it could handle the cases when there may be more than one match.

If you have an unbound text box on the main form (in this case for entering date). Create a subform with the other fields needed as bound controls. Put the subform on the main form and Link the unbound text box on the main form with with the subform date field. (using "Link Master Fields" and "Link Child Fields" properties of the subform)

What this will AUTOMATICALLY do is filter the subform on what's typed in the unbound box of the main form. If no value matches you'll just get a new record entry in the subform.
The subform can be setup as form OR datasheet view depending on which you prefer.

Just a suggestion if you are unfamiliar with abilities of linking subforms.

You can use the properties of the subform (e.g. navigation buttons yes/no, views allowed, etc) to format this setup in a number of different ways to suit different needs.