Modules & VBA :: Copy From Recordset Returns Blank
Oct 11, 2013
Below is a snippet of the code that I use, the user is promoters for a week, this has always worked and still does for any number less than 245. I dont know if 246 will work yet as I dont have that data.
Is there anything that would cause an integer with value 245 not to work? The query does still return values when I run it manually with value 245 and the strings for the query are all correct, hence why it works perfectly until you try and run it for week 245.
Code:
' Excel Objects:
Dim objXL As Excel.Application
Dim objWBK As Excel.Workbook
Dim objWS As Excel.Worksheet
Dim objRNG As Excel.Range
[Code] .....
View Replies
ADVERTISEMENT
Jun 11, 2014
I'm trying to assign the result of an SQL query to a variable using VBA in Access. The query returns a value but the variable which it is assigned to has a value of Nothing. Here is the code snippet:
Dim queryReturnID As String
queryReturnID = "select dbo_tbl_SupplierReturn.ReturnID from dbo_tbl_SupplierReturn" & _
" where SupplierID = " & lstPOHdr.Column(1)
Debug.Print queryReturnID
Dim RecordSet1 As DAO.RecordSet
Set RecordSet1 = CurrentDb.OpenRecordset(queryReturnID)
View 3 Replies
View Related
Aug 4, 2013
I have a DAO.recordset called "rsSQLIn". This comes from a csv file by:
Code:
strSql = "SELECT * " _
& "FROM [Text;Database=" _
& strFolder _
[Code]....
While the validation runs a boolean keeps track of validated input and errored input.
After validation the validated input is dumped in the table.
Now what I want is de saving the errored record from "rsSQLIn" to be copied to a new .csv file.
The problem I have is that I cant seem to get the current record from the recordset "rsSQLIn". How do I reference this? I need the complete set of 24 fields being the same within "rsSQLIn"
View 3 Replies
View Related
May 8, 2015
I am trying to copy a subform record set from one record to the next record. The data in the main table is copied over using this method;
Code:
Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant
v1 = Me![Today's Date].Value
v2 = Me!Insured.Value
[Code] ....
Can I integrate the copying of the subform data to the new record with this or do I need something different, and if so what?
It's a many to many relationship and I've tried adding the following line to the code:
v5=Me![Endorsements Umb XS].[UmbID]
same=v5
and it works but it only copies the first record out of the set.
View 14 Replies
View Related
Apr 18, 2013
PDF was generating and saving to the correct location, but when I open the PDF file ... it's blank =P am using access 2003 and acrobat standard 9
Code:
Private Sub cmdPDFByAssetNo_Click()
Dim MyFilter As String
Dim MyPath As String
Dim MyFilename As String
[code]...
View 2 Replies
View Related
May 12, 2014
I'm trying to write record set contents to excel. My query runs perfect in access query wizard, but recordset showing as null. My VBA code
Code:
Dim cnn As ADODB.Connection
Dim recordst As ADODB.Recordset
Dim strSQL As String
Dim strPath As String
Dim appXL As Excel.Application
Dim wb As Excel.Workbook
[Code] ....
View 5 Replies
View Related
Mar 24, 2013
When a query returns no records, the form appears blank. How can I make the form appear eventhough there are no records to show prior to inputting data.
View 5 Replies
View Related
Jun 7, 2013
I have a tabbed navigation form. I have a form on one of the tabs, [tab1frm] that a query that consists of 6 fields that are each from a different query.as the record source. This shows a percentage field, an image field, and a count field for current month and then then the same fields for year-to-date.
The problem is that when there are no records for the current month, the whole form [tab1frm] is blank. I read that if no records are returned this can happen, but since there are always records returned for year-to-date, I don't know why its happening. Is there any way to make blank fields show up or to show the most recent records so the form is never blank?
View 6 Replies
View Related
Jun 2, 2014
I'm relatively new to access. I have a database i created for my school project everything works fine until I try running a query with 2 or more tables, the queries return a datasheet with all the table headings but no data content, I ensured that there is data in the tables that I'm trying to join for the query.
View 6 Replies
View Related
Aug 14, 2015
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode:
? me.fsubsrNavSHLBHL("NavSH_QQ")
NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line:
fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window:
? me.fsubsrNavSHLBHL.Form.RecordSource
Select * from vsrNavigatorSHLBHL where Well_ID =91229
View 4 Replies
View Related
Jul 11, 2014
I have a form with a continuous subform whose RecordSource is a predefined query. There are a number of controls on the main form which allow for various filtering on the subform / query and a command button to export the data to Excel.Because I want to export exactly whatever the user has filtered using the form, I take a clone of the subform recordset and pass that as a recordset object to my export function.
I've just noticed, however, that a couple of the fields in the Excel spreadsheet are always blank, even though the corresponding records on the subform show values? I can't figure out why; there is nothing unusual or distinctive about these fields (2 x Text and 1 x Date/Time) other than the fact that the contents don't seem to copy? I do get the field names - just no data for them?
The data is visible in the subform, and when I run the query on its own, the fields are populated in the returned dataset as well? So why aren't they included in the RecordsetClone?
View 14 Replies
View Related
Jul 7, 2013
I want to write a email where there are 2 or 3 different ordernumbers for same email, i want to include the email in the mail part as single column table. how to do it? also can i use result of one recordset for other recordset?
View 1 Replies
View Related
Jun 8, 2015
Upgraded from Access 2003 to Access 2010. My DCount query was working fine in Access 2003 for many years but now I am having issues. Everything is written in VBA.
Routine:
1.) Insert records into table tblEmployee
2.) Query table tblEmployee to count how many employees are in each department using DCount.
Issue:
DCount returns 0 for all Departments.
Findings:
If I manually open table tblEmployee and edit an employees department (I actually named it the same department), DCount will then find 1 person in that department (the record I manually edited). It seems a manually edited record will be counted but anything written by the insert query is ignored.
View 14 Replies
View Related
Jul 18, 2015
VBA creating and have spent about 4 hours trying to figure out the error with my simple loop to update a few fields. As you can see from the attached picture, the "Recalc" sub simply stops updating partway through the loop.
View 4 Replies
View Related
Aug 5, 2015
I'm using the OnLoad event of a form I've got as a placeholder to load a report from the onclick event of a button on my continuous form, and loading a chkbox into the code as a string variable.For some reason, when the DB automates the procedure, I get a 'Type mismatch' error appear, but when I step through the code using F8 it works.
when I press F5 after the code has stopped I get the 'Type mismatch' error.The chkbox contains a value of '-1', and I've used debug.print to return the TypeName and VarType values, which were 'String' and '8' (which I took to mean 'String') respectively.
There are other variables being declared in the code, and I commented out the whole lot and put each one back in individually before running the code as a process of elimination, and the checkbox was the one that threw up the error again.
Code:
Dim Prod1stSend as String
Prod1stSend = me.chkProd1stSend
We are using the code to determine whether our clients have been contacted by us before, as this will decide which report is opened. I've tried setting the variable as Boolea and Variant too, and both of those still throw up the same error.
View 4 Replies
View Related
Aug 6, 2013
I wrote some code in Excel to open and run a module in Access. The code executes, but when I check the results I get no records, however if I just step back 1 line and rerun the query, I get records. Alternately, if I run the code twice it will get results the 2nd time.
The OpenCounter opens a Session in the terminal screen then performs a macro that writes to a temporary table.
Here is the code:
Dim db As ADODB.Connection, Rst As ADODB.Recordset
Dim Acc As Access.Application
Set db = New ADODB.Connection
db.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBPath
Set Rst = New ADODB.Recordset
[Code] ....
One thing I tried with limited success was to tell the OpenCounter to open and close the table after it was done writing to it, but that seems like a lazy solution and it also doesn't always work.
View 1 Replies
View Related
Sep 9, 2013
I have developed a private sub which automatically creates an email when a button is pushed.
The code is working well, but I need to insert Carriage Returns in the Body Text of the email just before the word "Please" where it appears in two places. Is this possible?
The code for the private sub is below.
Private Sub PublishROI_Click()
On Error GoTo Err_PublishROI_Click
DoCmd.SendObject acSendReport, "PSR_Rcd_of_Investigation_Rpt_Publish", acFormatPDF, Me.txtEmail & ";" & Me.txtRMEmail, Me.StorageEmail, , _
Me.txtPSRNum, _
[Code] ....
View 3 Replies
View Related
Mar 18, 2014
I have a form with a query set up as the record source. If there are records in this query, the form will display. When the user clicks a button, if there is another record in this query, the form displays the next record. When it gets to the EOF, it should close this form, but EOF is not being recognized (I run debug and it says it's false when the button is clicked)? I get run-time error '3201' after I click the button twice.
I know for a fact that there are 2 records in this query. So once I click the button on the 2nd record, the form should close.
Code:
Private Sub SelectTblMyMedButton_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("qryMedDataSelect", dbOpenDynaset)
[Code] .....
View 3 Replies
View Related
Jul 8, 2014
I have learned the basic looping technique for going through a record set from the following link. I need to know if my logic is on the right track. URL...
My question is a followup to a thread that was opened on this forum: URL....I want to do the following:
1) Use record set looping technique to fix a variety of incorrect naming conventions to a standard format
2) Update the table (or create a new table) from the updated record set values. (Is my logic going to update the table selected in the code I used to dimension the record set or will I need to do something else to make the changes available for other tasks after record set is closed ? After the naming conventions are fixed this data will be available for excel automation that I am working on and posting questions on another thread in this forum. HAHA I'm going code BANANAS)
3) rs.fields![fleetlocation] is used so many times, how can I make this a variable (what do I dimension the variable as?)
4) Use an AND statment with an if statment (how to do this with correct syntax)
Code:
sub loopandfix()
on error goto errorhandler:
strSQL = "tblUnionQueryResults" 'table was created from a union query but has inconsistant naming conventions for the fleet location name
[code]....
View 9 Replies
View Related
Jun 17, 2013
I am trying to open a child recordset but keep getting Error 424 "Object required".
Dim rstEmails As DAO.Recordset2, DBS As DAO.Database, rstFile As DAO.Recordset2
Set DBS = CurrentDb
Set rstEmails = DBS.OpenRecordset("Beldenemails")
Set rstFile = rstEmails.Fields("email").Value 'Error 424 here
End Sub
I've tried everything I can think of.
View 4 Replies
View Related
Jun 4, 2014
Is there a way of looping through a non update able continuous subform, and using information from each line.
Which creates a new record in another table? so if there are 3 records in the subform it creates 3 new records in the other table and so on?
View 3 Replies
View Related
Nov 11, 2014
Is there a possibility to use a shorter code for extracting one field from one record, than opening a recordset, getting the field, closing it and setting the recset to "Nothing" again? I'm doing this multiple times in my code and it seems a bit too much for what it's supposed to do.
Code:
sql = "SELECT tblAdFlgDaten.* FROM tblAdFlgDaten WHERE (((tblAdFlgDaten.AuftrID)=" & ABAuftrID & _
") And ((tblAdFlgDaten.Schritt)=2))"
Set RSnap = dbase.OpenRecordset(sql, dbOpenSnapshot)
If IsNull(RSnap.Fields(2)) Then
...
...
End If
RSnap.Close
Set RSnap = Nothing
View 4 Replies
View Related
Aug 6, 2013
How does one go about removing a recordset from a ListBox?
I have a list box that I want to toggle between using a query and an ADO RecordSet to populate the values.
Once I set the listbox .RecordSet property to the ADO.Recordset, I can't remove the values displayed in the listbox when I assign a query to the .RowSource property.
I suppose I can turn the .RowSource query to an ADO Query but I am being lazy and don't want to rewrite the query as a T-SQL query.
I previously thought the list box was pulling data from the .RowSource query but I realize I was wrong.
View 1 Replies
View Related
Sep 3, 2014
Is it possible to create a record set from a list box?
I have two list boxes list1 (customers) the can transfer records to list2. I want to take all records from list2 and use it to open a report, using customer id as where clause in my docmd.openreport statement.
View 4 Replies
View Related
Aug 21, 2013
As I am moving through my code, I'd like there to be a pop-up box which asks the user to choose from a list of dates. This list of dates only resides in a filtered recordset in the background.
Once the date is chosen, then my code continues onward..I know I could make some sort of pop-up form, but I'd rather not have to go in a design all that just for a list of dates...
1) Is there such a thing as a dropdown list on a Input box?
and even if there isn't...
2) Is there a way I can bind the column in a recordset to a dropdown list?
View 2 Replies
View Related
Jun 18, 2014
Im still struggling with working with recordsets.What i want to achieve is to loop through a continuous form recordset, Using the OrderDetailFK from the record set and the OrderDetailPK in the OrderDetail Table
Looking at the Status FK in the OrderDetail Table. IF the OrderDetail Status = 2 then i want to make the textbox enable property = False. I have made the bellow SQL string which is placing all the criteria i just need to link it into a recordset?
Code:
mySQL = "SELECT [StatusFK] FROM [tblOrderDetail] WHERE [OrderDetailPK] = " & rst!OrderDetailFK & " AND [StatusFK] = 2 "";"""
View 4 Replies
View Related
