Visual Basic Codes

[navdha88]

I have a database with 4 tables: user1996, user1997, user1998 and user 1999 .
I have created 4 queries which has only 2 necessary fields USERID and Date.
I used parameter queries to enter the dates.
I need to find out the "userid" for a period of time using Access form.

I created an access form(using design view). I have two text boxes where user can input dates,
'FROM_DATE'(for ex: 1/1/02) and 'TO_DATE'(12/02/02).
Also I have a 'FINDRECORD' button and a combo box with 'YEARS'((1996-1999)Which user can select the particular year).
My problem is nothing is getting selected in the datasheet view. I went to "Build Event". But having problems in writing codes in Visual Basic. Help is appreciated.


thx

 

[jrbarnett]

I would change the table layout so everything is in one table, there is only one date and users against each.

Then, you don't have to worry about which table to use and writing code becomes easy:

I would create a new form for displaying the results on, call it frmResults and it has the userID and date fields displayed.

For the "Find Record" button, the code then becomes:

DoCmd.Openform "frmResults", WhereCondition := "date between #01/01/" & me.cboYear & "# AND " & "#31/12/" & me.cboYear & "#"

You need to replace cboYear with the name property of your combobox. If you are in the USA, you will need to reformat your dates as mm/dd/ rather than the european date format.

For the From...To..., the code is remarkably similar to that above:
DoCmd.Openform "frmResults", WhereCondition := "date between #" & me.txtFromdate & "#" & " AND #" & me.txtToDate & "#"

You need to replace the Me.txtToDate and txtFromDate with the name of the text boxes for entering dates as well.

There are a couple of good FAQ's in the Access Forms forum which cover creating a report selection screen; but work just as well for opening a form. You may like to take a look at them.

John