Auto refresh a form

[keels]

Hi peeps, I have been learning access 2007 and have been trying to find out how to auto refresh a split form. I have tried everything from ontime event Me.Refresh Me.Requery with the interval set to 10000. I just can't seem to get the form to auto refresh. I have a column using a combo box in the table below the form that I need to auto refresh as it has a filter on it and would update based on the filter requirements. I have read that forms can be auto refreshed as if one were to click the refresh all button but I cant seem to do it. I'm a newby so be gentle and I don't know code so am pretty stuffed.
Any help would be amazing and really appreciated.

Regards

Akeel

 

[vbaInet]

So you don't continue wasting your time with the Split form, the split form is not at all programmable.

 

[keels]

That's probably why I couldn't find it oh dear my cheeks seem to have gone a bright red lol, is it warm in here or is it me hahahaha
Thanks vbaInet

 

[vbaInet]

You're welcome! It must be hot in there

 

[boblarson]

So you don't continue wasting your time with the Split form, the split form is not at all programmable.

That's a little bit incorrect. There are definitely some things you can code with it

1. Size of the Single form part using

Me.SplitFormSize = X

(where X is the number of TWIPS)

2. You can choose which part you print:

Me.SplitFormPrinting = acGridOnly
Me.SplitFormPrinting = acFormOnly

3. You can choose whether the Data Grid is editable or not:

Me.SplitFormDatasheet = acDatasheetReadOnly
Me.SplitFormDatasheet = acDatasheetAllowEdits

And then if you programmatically open the form in design view and then set these next ones and then save the form and reopen, you can set these:

   Me.SplitFormSplitterBarSave = True
   Me.SplitFormSplitterBarSave = False
 
   Me.SplitFormSplitterBar = False
   Me.SplitFormSplitterBar = True
 
   Me.SplitFormOrientation = acDatasheetOnLeft
   Me.SplitFormOrientation = acDatasheetOnRight
   Me.SplitFormOrientation = acDatasheetOnBottom
   Me.SplitFormOrientation = acDatasheetOnTop

And there are things which are programmable just like a normal form, but you can't do on the datasheet but there are things you CAN do like:

Me.DatasheetCellsEffect = acEffectSunken
Me.DatasheetCellsEffect = acEffectRaised
 
Me.DatasheetAlternateBackColor = vbYellow
Me.DatasheetBackColor = vbCyan
Me.DatasheetColumnHeaderUnderlineStyle = 8

You can also set the fonts (although the only way I could get it to work is if the last item I set was the DatasheetForeColor. If I didn't set that, they just didn't seem like they were working. So you could just set it to vbBlack as normal.

Me.DatasheetFontHeight = 12
Me.DatasheetFontItalic = False
Me.DatasheetFontName = "Calibri"
Me.DatasheetFontWeight = 700
Me.DatasheetForeColor = vbBlue

And more:

Me.DatasheetGridlinesColor = vbRed

Now there is also functionality using things like this:

Freeze column(s)

Me.ControlNameHere.SetFocus
DoCmd.RunCommand acCmdFreezeColumn

Hide Column(s)

Me..SetFocus
DoCmd.RunCommand acCmdHideColumns

And that is just the DATASHEET portion. The single form part can do a lot of what you can do on a regular form.

So, I would say that yes, it has its limitations. But it is FAR from being NOT AT ALL Programmable.

 

[keels]

Wow thanks Bob,

There seems a lot you can do I had noticed that I was able to achieve some things through trial and error but that is a mighty long list. I wonder if you could take a look at my second thread and see if there is a way of achieving the results I need.

I have attached the DB so you can see what I mean, am a novice so my explanations are sometimes a little vague , thanks in advance.

Regards
Keels

 

[vbaInet]

So, I would say that yes, it has its limitations. But it is FAR from being NOT AT ALL Programmable.

Perhaps that was a bit of a harsh statement on the poor Split Form . I should have said it provides very little programming capabilities. They have their use though.

 

[keels]

Perhaps that was a bit of a harsh statement on the poor Split Form . I should have said it provides very little programming capabilities. They have their use though.

I hear ya lol

Thanks

 

[vbaInet]

So what do we need to do to reproduce the problem? I've got your db open now.

 

[keels]

So what do we need to do to reproduce the problem? I've got your db open now.

Ok thanks,

well if you open the form venus dispatch system and go to new record and type a name into the passenger combo box hit enter a query will run and show duplicate names of passengers that have previously booked jobs, but I only want the names of passengers that match the name that you input.
So if someone called ken calls the query will return all people named ken who have previously called so I don't have to type out all the details again.

 

[vbaInet]

I still don't get it. What are the exact steps I need to take? Tell me what I should do to actually see the refresh problem.

 

[keels]

I still don't get it. What are the exact steps I need to take? Tell me what I should do to actually see the refresh problem.

The easiest way is to show you in steps with pics they say it better than me.

Step 1 Book a new record.

Step 2 Enter the passengers name.

Step 3 See the query return passenger duplicates

It's those duplicates, there are to many names I want the query to return only the name in step 1 as highlighted in step 3.

Hope this helps mate.

 

[vbaInet]

Ok. Has this got anything to do with your "auto refresh" problem? It seems to be a totally different issue.

There's no problem with the duplicates because they are not duplicates. The query itself contains those records and that's exactly what the split form (and the parent form) is displaying. If you want unique records (i.e. the same records based on your combo box) then you should base your form on the recordset of your combo box but this obviously isn't what you would want to do.

 

[keels]

Correct!
If you have a look at my reply to Bob, I asked him if he could take a look at my second thread but posted the db here for convenience, my second thread asked if there is a way I could modify a query to return a specific number of records based on the name entered in a combo box on a form. Now I know I can achieve this but I have to tell access what I want it to do in a language it understands, that is what I'm working on, I'm trying to understand the language access understands and hopefully I'll get there.

But thanks buddy for having a look and giving me a hand.

 

[vbaInet]

So since you have a second thread what is the URL to that thread? We can answer your questions there.

 

[keels]

http://www.access-programmers.co.uk/forums/showthread.php?t=217804

 

[smig]

Welcom Keels

Refresh will refresh the records = save any unsaved data.
Requery will requery the object you want.

Though they sound as doing similar things they don't.

 

[vbaInet]

Refresh will refresh the records = save any unsaved data.
Requery will requery the object you want.

The main distinction between Refresh and Requery is that the Refresh method will only reflect changes made to the currently viewed record whilst the Requery method requeries the database for the entire recordset.

But this isn't what the OP (or consequent questions) is about.

 

[missinglinq]

...Refresh method will only reflect changes made to the currently viewed record...

Actually that should be "changes made to the records in the current set," not the currently viewed record.

And it should be noted that Refresh doesn't include Records that have been added or deleted since the RecordSet was last Requeried.

Also, Records that no longer satisfy the Criteria of the Query or Filter will not be excluded.

Requery is usually the way to go. People frequently try to avoid it because Focus returns to the first Record in the Form.

To Requery a Record Source and then return to the Current Record:

Where [UniqueField] is a Field unique to only one Record:

Where [UniqueField] is Text

Dim UF_Rec as String
   
   UF_Rec = Me!UniqueField
   Me.Requery
   Me.Recordset.FindFirst "[UniqueField] = '" & UF_Rec & "'"

Where [UniqueField]is Numeric

Dim UF_Rec as (Fill in number type here)
   
   UF_Rec = Me!UniqueField
   Me.Requery
   Me.Recordset.FindFirst "[UniqueField] = " & UF_Rec

Linq ;0)>

 

[vbaInet]

Actually that should be "changes made to the records in the current set," not the currently viewed record.

Correct! That's what happens when you're eating a roast dinner and typing on here at the same time