Automating a record based on day of week 1

[Navo720]

Hello...This is my first posting. I would like to say thanks to everyone in advance for such a great site. I am very new to programming would like some help. I have two questions. First,I am working with two tables in Access 2000. One of these tables (table 1)is a lookup table with information for day of week (Sun, Mon, etc...)as field 1 and other data for field 2, 3 ,etc. I would like the other table (table 2)to pull up the fields based on the date that I enter. For example, if I typed 5/25/04 in a textbox in table 2, how do I get access to pull field 2, 3, etc... from table 1 for Tuesday's information. Second, how do I clear out a bounded textbox field when I exit a form. I tried setting the form's on exit procedure with field.text="" but that didn't seem to work.
Thanks for whatever assistance you can give!!

 

[ZmrAbdulla]

Hi,
Put an unbound textbox on the form (table 2). Controlsource= YourDateField
Format = dddd
This will display the day of the date entered in the date field.
Ok
Secondly
Put this code in the Enter Event of the new Textbox

Set DB = CurrentDb
rst = Recordset
Set rst = DB.OpenRecordset("Select [red]day[/red] from table1 where day= '" & Newtextbox.Text & "'")
'Pull information from table 1
text1 = rst.Fields("Textinfo1").Value
text1 = rst.Fields("Textinfo2").Value

Hope this help
Post back if you are unable to solve


Zameer Abdulla

http://www.geocities.com/zameerabd/

http://www.mtkhoory.com

 

[Navo720]

I really appreciate your help. I tried this but I seem to be doing something wrong. I keep getting a debugging error. When I tried applying it to the database I'm working on, the error was on the set DB, so I tried it on a completely new blank test database. The debug error points to the set rst line.
As I said before I am completely new to programming. Could you also point me to a good book to learn programming from beginning to end. Everything I have looked at so far expected prior programming skills.

 

[PHV]

Provided [Table 1].[Field 1] is populated with the exact short day names in respect with your regional settings, you can try something like this (in the AfterUpdate event procedure of the date textbox):
If IsDate(Me![TextBoxDate]) Then
MsgBox DLookUp("[Field 2]", "[Table 1]", "[Field 1]='" & Format(Me![TextBoxDate], "ddd") & "'")
End If

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ZmrAbdulla]

Hi,
I have made a working sample somewhat you need. May be useful for you. If you want I can send it to you.

zameerabulla@hotmail.com

Regards

Zameer Abdulla

http://www.geocities.com/zameerabd/

http://www.mtkhoory.com