Access Programming beginner questions

[Djblois]

I am pretty good at programming in Excel but not my job needs me to start programming in access as well and I am having trouble. Especially when it comes to quearies and SQL in VBA. Here is the code I am trying to get to work:

Private Sub txtApptDate_Exit(Cancel As Integer)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qryLoadDate")

strSQL = "SELECT [Scheduled_Appts].* " & _
"FROM [Scheduled_Appts] " & _
"Where [Scheduled_Appts].Appt_Date='" & Me.txtApptDate.Value & ";"

qdf.SQL = strSQL
DoCmd.OpenQuery "qryLoadDate"

Set qdf = Nothing
Set db = Nothing


End Sub


What I want it to do is when the user changes the Date in txtapptdate then the data on the form changes. I have a bunch of textboxes on the bottom that I want to fill with data. The query may return multiple items therefore I need it to fill them all in. A few questions:

1) I am currently getting an error on this line:

Set qdf = db.QueryDefs("qryLoadDate")

do I need to set up a query in Access with that name?

2) How do I get the query to fill in the form as opposed to open a second query window?

3) Do I need to use a loop to get it to fill in all the lines of data? or will it do that automatically?

[CreganTur]

Welcome to the wonderful world of Access :bigdance2

Using SQL in VBA is something that stumps all beginners in one way or another, so don't feel bad about it. That's why we're here!


1) do I need to set up a query in Access with that name?

The short answer is "yes". When you try to set a varriable to a QueryDef, the name defined within the parantheses must be the name of an actual query that exists as a query object in Access. But, I don't think you need to do this. See answer to #2.


2) How do I get the query to fill in the form as opposed to open a second query window?
Honestly, I would go about this a completely different way. First, a little clarification: you say that your users will change the date in Me.txtApptDate (you don't have to use the .value method; by default referring to a Form object will pull its value. What you did is good coding discipline, but most Access devs- myself included- just drop the extra keystrokes). I am guessing that Me.txtApptDate is a textbox, due to the txt prefix. Have you considered using a combobox instead? That way you can avoid the errors that would arise from users typing in the date incorrectly.

You could set the combobox to query the dates from one of your tables. But, after you setup the query to do this change to SQL view and change SELECT to SELECT DISTINCT- this will ensure that it only pulls each unique date once.

Now, I would then open up the query that is your Form's recordset in design view. Go to the Appt_Date field and, in the criteria section, type in [Forms]![FormName]![FormObject]- change FormName to your form's actual name, and FormObject to the name of the object that your users will choose a date from. I would highly suggest using a combobox! What will happen is that the query will pull the records that match the value of the combobox.

Next, still looking at your query in design view, click Query-> Parameters. This opens a window where you can declare parameters for the query and set their data type. In the left field type in the same [Forms]![FormName]![FormObject] you used a second ago, and then set the data type. I'm guessing you'll want to set it to Date. Save and close out of query design view.

Back to the form to finish up one last detail. In Form design view, click on your combobox and select the Change event and use this code:
Me.Requery
This will cause the recordset that your Form shows to be updated whenever the combobox is changed.

Now, when you first open your form everything will be completely blank, but when you make a selection in your combobox it will bring up the records you want!

Does this do what you were looking for?


3) Do I need to use a loop to get it to fill in all the lines of data? or will it do that automatically?
Using the method I outline above, you will need to do nothing. All matching records will appear because you are requerying the form's recordset.

Please let me know if you have any problems with the suggestions I provided.

HTH:thumb

[Djblois]

1) I am using Access 2007 which opens a Calendar for user to select dates. I think this is the easiest way. Because the amount of dates would probably be huge.

2) You say I can set the combobox to query the dates. I assume I can set the textbox to do the same?

3) How would I set it up to query my table? Do I need to create a query and then attach it?

4) What do you mean by this, "Now, I would then open up the query that is your Form's recordset in design view."?

Remember, I am using Access 2007. Thank you for your help!

[CreganTur]

1) I am using Access 2007 which opens a Calendar for user to select dates. I think this is the easiest way. Because the amount of dates would probably be huge.
I've you're using the ActiveX Calendar control, then you can set its click event to place the selected date into a textbox of your chosing. Then you can have the query that is your form's recordset pull the value from there instead of a combobox. The code would look like:
Private Sub axcCalendar_Click()
Me.txtBoxName = Me.axcCalendar.Value
End Sub
Just replace axcCalendar with the calendar's actual name, and replace txtBoxName with the textbox's actual name.


2) You say I can set the combobox to query the dates. I assume I can set the textbox to do the same?

No, a textbox cannot query anything. But, as stated in my answer to #1- you can put the selected date from your calendar into the textbox.


3) How would I set it up to query my table? Do I need to create a query and then attach it?
Go to the queries tab on your database window and create a new query based on the table. Select the fields you want it to have and set it up so the date field's criteria pulls from the textbox on your form. Then save the query and give it a unique name. Then open your form in design view and open up the properties sheet. Click on the Data tab and then click on Record Source- select your query's name from the drop down.


4) What do you mean by this, "Now, I would then open up the query that is your Form's recordset in design view."?

My answer for #3 tells you how to set a your form's record source to a new query. The record source is a table or query that records are pulled from to populate bound objects. Bound objects contain data that is automatically displayed, based on the form's recordset.

There are also unbound objects- they are independant from the recordset. The textbox you create to hold the date from your calendar should be an unbound textbox. When you change the value of a bound textbox, you change that field's value within your table. When you change the value of an unbound control, it changes nothing but the control's value.

Generally it is considered bad practice to setup a form that is directly connected to your table, so most people use queries as form record sources.

Now, what I meant by the quote you provided is that you should go to the query that is your form's record source- go to the queries tab of the database window, select the query, and then click on the design button. This will open it in design view so you can make changes to it.

Did that answer your questions?

[Djblois]

Is there a way I can show you a copy of these database? I did exactly what you told me and it isn't working right. My form now ends up being blank - all the textboxes don't even show up. I only have one line of data in it but it doesn't do anything if I filter to that date.

[CreganTur]

You'll need to use the 2003 format- I don't have 2007. Just zip the database and upload it here on the forum. Just be sure there isn't any sensitive info ;)

[Djblois]

here you go. just to let you know if you attach a date to a textbox in Access 2007 then it has a calendar control attached to it.

[CreganTur]

I couldn't do anything with your example DB because you didn't include any sample records.

Instead of relying on the calendar control that access 2007 creates automatically, perhaps you should use a real calendar control.

I think the problem is with the requery. I've attached a sample DB that shows how to do this. There are records on the dates 1/1/09 - 1/8/09; there are 2 records for 1/7/09.

I moved the requery code to occur just after the value of the calendar is put into the HireDate textbox.

Take a look and see if this example helps you.

[Djblois]

Cregantur,

I thank you for all your help; it is getting there. The reason I wasn't using the calendar control was because I could not find it on the ribbon. However, I have found how to insert it now and I have. Thank you. But now I am having a weird issue. I attached it to a label so a user has to change the date using the calendar. But it won't allow me to click the buttons on the calendar. I am looking all through your database you sent me and I can't find anything different that may be the issue.

[FrymanTCU]

Randy,

That Calendar control is cool, is that available in 2000? If so how do I access it, I found the code reference to it but I don't know if it is an add-in I am missing or what.

Thanks,
Rich

[OBP]

Yes it is, Form in Design View, Main Menu>Insert>ActiveX control>Calendar Control

[CreganTur]

But it won't allow me to click the buttons on the calendar. I am looking all through your database you sent me and I can't find anything different that may be the issue.
Have you fixed this, or is it still an issue?

I can't think of any reason why it won't work... but I can't test it on 2007 :dunno

[Djblois]

It is still an issue. I will do a search on the web.

[Djblois]

It is weird. I removed the form being attached to the query and now I can click on the calendar control.

Here are my SQL statements in the query:

PARAMETERS [Forms]![frmScheduled_Appts]![lblApptDate] DateTime;
SELECT Scheduled_Appts.Appt_Date, Scheduled_Appts.Customer, Scheduled_Appts.Carrier, Scheduled_Appts.[Order#], Scheduled_Appts.Loader, Scheduled_Appts.Sch_Hour, Scheduled_Appts.Sch_Minute, Scheduled_Appts.[Sch_PM/AM], Scheduled_Appts.Arr_Hour, Scheduled_Appts.Arr_Minute, Scheduled_Appts.[Arr_PM/AM], Scheduled_Appts.Dep_Hour, Scheduled_Appts.Dep_Minute, Scheduled_Appts.[Dep_PM/AM]
FROM Scheduled_Appts
WHERE (((Scheduled_Appts.Appt_Date)=[Forms]![FrmScheduled_Appts]![lblApptDate]));

Tell me if you see anything wrong?

[CreganTur]

Your SQL looks good... Are you sending the date from the calendar to a label object? I ask because lblApptDate- the prefix lbl to me says it's a label. If it is a label, then that could be the problem. I'm not sure if SQL will pull a label's caption.

I may be way off base here- it's the only thing that stuck out to me. It's just so odd that you're having trouble with the calendar control.

If this doesn't fix it, then you may need to see if you can find someone else with 2007 to check it out.

[Djblois]

Cregontur,

I got it mostly working now. However, it will only work for a few clicks. I click on one date and it works and I click on another date and it works then it stopped working. I have inputted some data for you to test it. It is really weird. I also saved it in the old format again. Thank you for all your help!

[Djblois]

I figured out why it stops working. It locks up when you click on a date that has no data and then I cannot go back to a date that has data. Is there a way to prevent it from locking up?

Ok I Figured that out also-It will not lock up if I allow adds. Only problem is I dont want to allow adds, I do want to allow edits though.

[CreganTur]

I see what you mean... For me the error only occurs whenever you click on a date that pulls null records from the query. I can click all I want between 23, 24, and 25, but as soon as I go to a date that doesn't have matching appointments, it locks up exactly as you described.

I noticed that you have your query pulling its value directly from the calendar control- I never thought to try that, but it works great. By doing that you can completely bypass pushing the date to a textbox or other object. I originally thought that might be part of the reason for your problems, but it isn't- I did the same thing to the example DB I uploaded earlier, and it works perfectly!

NinjaEdithttp://img293.imageshack.us/img293/9060/ninja3od8.gif: I found it! the error is caused by the fact that you set the Allow Additions property of your form to No. Change it to Yes, and the problem goes away. It will show a blank record, but that's a small price to pay to get it to work!

Edit Again: Looks like you figured it out before me.

I'll think about this and see what options I can come up with for you to be able to use the calendar control, and prevent your users from clicking on invalid dates to prevent lockup.

[CreganTur]

Here you go- this code will cause a message to pop up whenever someone clicks on an invalid date.

Private Sub cldrApptDates_BeforeUpdate(Cancel As Integer)
Dim db As ADODB.Connection
Dim rst As ADODB.Recordset
Set db = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "SELECT DISTINCT Appt_Date FROM Scheduled_Appts WHERE Appt_Date = #" & Me.cldrApptDates.Value & "#;" _
, db

'rst.MoveFirst
If rst.BOF Then
MsgBox "Invalid Date"
Cancel = True
End If
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

I set the form back to Additions = No, and used that code. It worked perfectly!

See if this fixes your problems.

[Djblois]

Thank you,

I am getting an error on this line:

Dim db As ADODB.Connection

It keeps saying "User Defined type not defined"

Also, is there a way I can limit a textbox to only allow someone to input numbers between 1-12
and in the comboboxes, I don't want a user to be able to enter anything besides what pops up in that list.

[CreganTur]

I am getting an error on this line:
Whoops- my fault. It requires a reference to Microsoft ActiveX Data Objects- set it to the lastest version you have. Click Tools-> References in the VBIDE window.


Also, is there a way I can limit a textbox to only allow someone to input numbers between 1-12

Take a look at the IsNumeric function in Access Help.

Or you could use SelectCase... Use the textboxes BeforeUpdate event and try something like this:
Dim InputCheck As String

InputCheck = Me.txtBoxName

Select Case InputCheck
Case "1","2","3","4","5","6","7","8","9","10","11","12"
'do something
Case Else
MsgBox "Error!"
End Select


in the comboboxes, I don't want a user to be able to enter anything besides what pops up in that list.
Click in the Data tab under the properties window for your comboboxes. Change Limit To List to Yes.

HTH:thumb