Excel 2010 vba

What I have:
I have a defined range that i need to import data into an Access table
In the excel data  the range is starting at  Column "K row 5  TO  S"
the rows in that data range are never the same


What I need:
I need to make sure the code captures and imports K5 to  S(whatever)

' Replace WorksheetName with the actual name of the worksheet
' in the EXCEL file
Set xls = xlw.Worksheets("WorksheetName")

' Replace K5 with the cell reference from which the first data value
' (no-header information) is to be read
Set xlc = xls.Range("K5") ' this is the first cell that contains data  and Goes to  S5



Set dbs = CurrentDb()

' Replace QueryOrTableName with the real name of the table or query
' that is to receive the data from the worksheet
Set rst = dbs.OpenRecordset("QueryOrTableName", dbOpenDynaset, dbAppendOnly)

' write data to the recordset
Do While xlc.Value <> ""
      rst.AddNew
            For lngColumn = 0 To rst.Fields.Count - 1
                  rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
            Next lngColumn
      rst.Update
      Set xlc = xlc.Offset(1,0)
Loop

rst.Close
Set rst = Nothing

dbs.Close
Set dbs = Nothing

Select all Open in new window

As Microsoft announced to roll-out Office 2019 in fall of 2019 which will include MS Access 2019 as well. I need assistance in getting information regarding the following:

1. Existence of Backward compatibility with MS Access versions (2010 to 2016)
2. What unknown compatibility issues an MS Access 2010 app user and or maintainer should be aware of before making a decision to upgrade MS Access 2010 based app to MS Access 2019?

The ONLY thing I am sure at this moment is that MS Access 2019 will on runs on Windows 10.

Guidance and suggestions will be appreciated.

Hi,

I have an Access FE and SQL 2014 BE and i encounter the following issue:
1. I have a bound form
2.  if the record is a NEW record, never saved, and I set some textbox to NULL explicitly (me.someTextbox = Null) and then I save the record  using Dirty=False, it gives a #deleted for the entire record
3. I've checked if it could be some trigger etc... but it doesn't sound so (i've disabled them all for testing)

Any help would be appreciated

Joseph

How can I have a task performed during the number of copies is printed?  Right now I have a combo box and the user selects number of copies, but in some instances I have a barcode on a label and the user will not want the same barcode on every label so I need it to change in between the number of copies selected.  Below is what I have now.  

docmd.printout,,,,Forms![main]![cbocopies]

Select all Open in new window

Hello Experts:

It's been a while since I used "case statements" in MS-Access.

I need some assistance with using a wildcard (*) in a case statement.   Below is what I have:

Public Function Q8_If_Married_Convert(Q8_If_Married As Variant) As Variant

        Select Case Q8_If_Married
        
           Case "Resides with spouse":                                                                   Q8_If_Married_Convert = "Resides with spouse"
           Case "Separated":                                                                                   Q8_If_Married_Convert = "Separated"
           Case "Separated due to other reasons (e...g... deployed)":                    Q8_If_Married_Convert = "Separated due to other reasons"
           Case Else:                                                                                               Q8_If_Married_Convert = "No data available"
           
        End Select

End Function

Select all Open in new window

In this example, the problem lies occurs for the 3rd statement.   Rather than having a hard-coded case equal to "Separated due to other reasons (e...g... deployed):", I prefer using the case "Separated due to other reasons*" (ending with the wild card).   The key challenge how the data entry person entered the reference to "deployment".   That is, in this case, too many periods were used in the "e.g." reference.   Alternatively, in other cases, the separation reason may not be "deployment" but could be "XYZ" reason.

Can anyone offer some assistance with a) recommendation how to tweak the case select statement or b) offering an alternative that allows me to convert all possible outcomes for the "Separated due to other reasons*" scenario?

Thank you,
EEH

Hi all I am back again with another problem. :) (Eye rolls everywhere) :)

I have created queries and built reports around those queries which I need to print. I have got it to a point where it is displaying the information but it is also displaying all the information for all the records. How can I get it to only print 1 record.

I have tried different things by using the WHERE statement and i have tried filtering and also tried recreating the report.

The reports that are required are in the following db

The reports are Test and TESTCS built on query1Test and queryTESTCS

Thanks for your help

Hi,

We are trying to move to SharePoint online. We have an access database which has a backend database and front end link. I copied all the files from the file server to the Sharepoint document library and connected it as a network drive to my PC. The front end is giving an error saying "Runtime Error 3043. Your network access was interrupted. To continue close the database and then open it again."

I tried to link the database as external data source but it gives an error saying cannot link to an internet-based source. Can someone advice how can we fix it?

Regards,

Ajoy

I have a form with a date field on it named txtRecordDate. In another field named txtFirstOfWeek I want to display the first date of the week in the txtRecord Date.  So for example, if the txtRecordDate shows 6/14/2018 I want the field txtFirstOfWeek to display 6/10/2018.

What is the control source for the txtFirstOfWeek field?

I have a form that has a datasheet subform on it.  It the first field on the datasheet I have an unbound field named txtLineNumber.  When the form is opened I want the records to show the line number starting with "1".  So, it would look like:

1
2
3
4
5
6
etc.

How can I do this?  I've tried making the text field control source =1 and everything I've read says to make the Running Sum property and select Over All but I don't see a running sum option.  

FYI - the record source for the datasheet subform is a query if that makes a difference.

MS Access Query: how to add rows between two dates until end date is reached; for example, if a record contains the following:
PersonID, Start Date, End Date
123            1/1/18         1/5/18
Note: the PersonID could have multiple/different start and end dates.

Can anyone assist in  correcting my union query so that it adds rows until it reaches the End Date:
PersonID, Start Date, End Date
123            1/1/18         1/5/18
123             1/2/18         1/5/18
123             1/3/18         1/5/18
123            1/4/18         1/5/18
123             1/5/18         1/5/18

This is what I have so far but it returns only one additional date:

SELECT tblTable.[PersonID], tblTable.[Start Date], tblTable.[End Date]
FROM tblTable
UNION ALL SELECT  tblTable.[PersonID], DateAdd("d",1,[Start Date]), [End Date]
FROM tblTable
WHERE tblTable.[Start Date] < tblTable.[End Date];

On the form, I have a checkbox and the click event as shown below. On form load, I have the checkbox value set to false.

Private Sub Check2458_Click()
Check2458.Value = True
ConditionReceived.Value = "Received  and working " 

The checkbox does not get checked on the first click and the code did not get executed.
Any help resolving this issue is appreciated.

Hi
I have a form where I have a dropdown control to help filling up (or correcting) a field [fRefSeccao]. The form contains 2 other fields, [fRefRegiao] and [fRefSector] that must work as filters for this dropdown so the user will only be able to see the [RefSeccao] list of records that match the [RefRegiao] and [RefSector] table fields linked to the  [fRefRegiao] and [fRefSector]  controls.

The form (in design mode) looks as below:


On the [fRefSeccao] source table, I do indicate a Select, looking like this:

SELECT qConfereSeccao.codRegiao, qConfereSeccao.codigoSector, qConfereSeccao.refSeccao, qConfereSeccao.nomeSeccao
FROM qConfereSeccao
WHERE (((qConfereSeccao.codRegiao)="13") AND ((qConfereSeccao.codigoSector)="12"));

and it works perfectly.

Now I want to replace the

       WHERE (((qConfereSeccao.codRegiao)="13") AND ((qConfereSeccao.codigoSector)="12"));

by a dynamic search on the field forms [fRefRegiao] and [fRefSector], like

       WHERE (((qConfereSeccao.codRegiao)= fRefRegiao) AND ((qConfereSeccao.codigoSector)= fRefSector));

but it does not work as it does not recognise them

How do I do it?

I'm trying to get an ancient program running, which uses Access 97 runtime. Everything appears to be in place, but when I try to run it I get a message that no Access license file is found. Installing Access 2000 didn't do anything helpful. The program is long dead and support is completely unavailable.

I have a large pile of install floppies for various versions, but no idea what the Access runtime license looks like (or why it didn't install when I ran the most recent set of floppies).

Can anybody help me out with the name/structure/whatever for an Access97 runtime license file?