Posts by spesty

Re: Using Option Buttons in a form to run a query

wouldn't I still have the same problem regardless of what command button ect that I use. I would still have to allocate a value to the select query which would enable me to select all dates

Hi

I am having a drama trying to get a select query to run with data selected using a group of option buttons from a Form.

Basically I have 10 option buttons each with a year associated with it. My goal is to enable the user to select a year then click a command button to display projects associated with that year. I also want to be able to have the option of displaying projects for all years.

I seem to have got the selecting specific years thing right using a IIF statement in the criteria part of my query. However, I am not able to get all the years to display at once. Is there a null value or blank value you can assign to the IIF statement to just display everything, like what would happen if you didn't use any criteria statements?

I am sure it can't be all that hard but it is driving up the wall

Cheers
Darryl

Thanks Tom,

all seems to be work fine

Hi

I currently have a workbook which has multiple worksheets, each sheet having an individual name based on a date e.g. 071004, 081004.

I am try to passing information from each of these sheets to a single sheet so the data can be loaded into a new software package. Currently my script is able to select the current active sheet and pass the information but I am not to sure how to select the next sheet and so on and so on

I am sure it is quite simple but am not to sure of the syntax, any assistance would be appreciated

Thanks

Hi all,

was wondering if anyone could help me with an image problem. I am trying to add and OLEobject (jpg file) to a table. I am able to select the file fine and import it to the table. However, when I view the image in a report I get extra black space with the image. Thus it doesnt fit into my bound image box on in my report.

I have cropped the image before importing into Access and this doesnt seem to make any difference. I have also tried to edit the object (jpg) from my table, once again with no success.

any assistance would be very much appreciated

Thanks

Hi All

I have a problem with a calendar form I have created. When the form is active I am unable to scroll through my spreadsheet to view the dates present.

I am assuming there is a form property which would enable me to still scroll through my spreadsheet before selecting the desired date

any ideas?

Thanks

Hi Dave,

Will I have to add similar formating in the following script that you help me with as I now get the following error:

Run-Time error 1004

Method Range of object '_Global" failed

strTime = Format(Range("B2"), "dd/mm/yyyy")

Set rRange1 = Range(Columns(1).Find(What:=strTime, After:=rRange1, _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False), _
Columns(1).Find(What:=strTime, After:=rRange1.End(xlDown), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False))

Thanks Dave

I give it a try

Hi again,

I am still having problems with this code. It seems to work fine if the data isnt Date formatted.

The cells.find command just doesnt seem to work at all. I have been monitoring the attempted Find in the spreadsheet window and it doesnt select the cells while the script is running. However if I go into the spreadsheet and manual use the Edit - Find menu the variable I am using in the script (Cells.find) is present and it works!!!!!

I have a feeling it is a formating problem but I can't find away around it

anyway ideas would be greatly appreciated.

Hi all,

I am trying to get a True/False value back from a search within my spreadsheet for a Date, see code below:

Boolean_variable = Cells.Find(What:="21/03/2003", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select

The code seems to work fine if the date value is present and the value is True within the spreadsheet. However, if the date value isnt present (False) then I get the following error message:

Run Time error "91"

Object variable or With block variable not set

Any ideas how to overcome this

Thanks

Hi will,

well I tried your format statement and it worked fine

thanks for that...I hate it when its that simple

cheers

Hi Will

sorry about starting a new message, but I posted something back to you last night regarding this issue but it didnt seem to display!!!

I will have a try at reformation the date as you have displayed in your last message

What I am trying to do for more clarification is
1 to produce a date value, which is passed to a variable, using the Calendar control in VBA.

2 I then am trying to uses this variable to search through my spreadsheet, which contains date information as dd/mm/yy

3 my intention is then to use the selected cell in my spreadsheet to select all records that occur on this date so as to display the data as chart

I hope that makes more sense

thanks for the feed back

Hi all,

Am currently trying to clip off two numbers in a data variable created by the Calendar control (addition control in VBA)

The variable created from this control looks like:

17/12/2003

but I require

17/12/03

I have tried to FORMAT the date to "short date" but am still left with 2003

is there a simple way of removing this?

thanks

Hi all,

does anyone know if you are able to edit the year format in the Additional controls?

I have data with only 03 for the year and the calendar only appears to output 2003

Hopefully someone can help

ta

Hi all,

I am trying to select a group of cells in one column (A) and then another group in another column (D) to enventually place them into a chart.

I was wondering if where is a easy way to select the top cell in the group (column A) and then continue to select down the column until the record changes. I guess like using an IF statement e.g. if cell 1 = cell2 THEN select, ELSE dont select

I am not to sure it this make sense but if anyone can help that would be great!!

thanks

Hi

Thanks for the feed back but unfortunately I have already set the index properties for all my primary keys to "duplicate ok"

Any other suggestions ? :yes:

Hi All,

I have a form with a subform that allows data to be entered and retrieved (by entering/selecting existing values in the primary key combo boxes), but when trying to edit or add new data in the subform, it doesn't allow this operation to occur. The error message is:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the date in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

I have also checked that AllowEdits, AllowDeletions & AllowAdditions were set to Yes, and that Cycle was set to CurrentRecord not All Records, within the form and subform properties (suggestions found in Microsfot Access help).

This is an old database that I'm trying to get working properly. I suspect that the table relationships (and maybe even some of the database design - unfortunately I don't have the luxury of time to get this working again - just need to get it working properly) might need modifying, but my attempts so far haven't made any difference.

Not sure what the next step should be - does anyone have any suggestions?

Thanks.

Hi Dave

Thanks for the info, however is there a way of setting the A1:B10 to be a variable which calculates how many cells contain data and then only copies those cells with data to the new worksheet.

Hope that makes sense

Hi

I was wondering if anyone could give me some idea about the best and most efficent way of copying data from a cell in "sheet1" to the corresponding cell in "sheet2" for a significant number of row and columns?

I have been playing around with the Range Object but keep finding I get lost with all the properties and methods

Thanks

Thanks guys

hopefully one day I get a decent grasp on things