If/then/Else using me.recordsource "Select tbl" string no longer works

[GinaStar]

was using if/then behind a button to switch from a 'current' to an 'archived' table on a ticket search form. Has worked perfectly since 1997. All of a sudden, will not let me add the record sources upon reprogramming this year (I reprogram every December & upgrade). can email full code if needed. Would love some suggestions..but am wondering if since have renamed some fields to comply with naming conventions and also to make easier to work with, if now too long.

actual code is:

If Me.cmdSwitch.Caption = "Active" Then
Me.RecordSource = "SELECT tblDataCustomer.strCustNa>>"
Me.cmdSwitch.Caption = "Archive"
Else
Me.RecordSource = "SELECT tblDataCustomer.strCustNa>>"
Me.cmdSwitch.Caption = "Active"
End If

Note: record Sources truncated for convenience & error codes removed

btw, I'm actually a bookkeeper & personnel manager who programs on the side with no 'official' training, just what I could teach myself :/

 

[BSman]

Test your form with both record sources. Your code example suggest that you are using the same table as your record source. If so, here are a few suggestions:

Try editing the form, selecting the complete table as your record source. Go through each control on the form and make sure it is using a valid field. Then make sure that your VBA code in back of the form references each field correctly.

If the data is all in the same table and you are just using a portion of the data, it might be easier to set up your form and button to do the following. I'm assuming that there is a field in the table that determines whether a record is Active or Archived (I'll call it DateClosed).

1. Assuming that you want the form to open with current records, in the OnLoad property of the form, put the following code (the entire table is the record source for the form):

me.FilterOn = True
me.txtDateClosed = Null
me.Requery

(You may have to play around with the matching value for the control holding the DateClosed [me.txtDateClosed], since sometime looking for null values can be confusing to VBA.)

2. When the user clicks on the Archive/Current button, test for the button's caption and use the same code as above except that for archived date you test for:

me.txtDateClosed > #1/1/1980# [or whatever date you want]

 

[GinaStar]

not exactly. This form is used to search for a specific ticket (and show the details such as what driver hauled that load, when it was done, what customer, has it been invoiced, etc.) in one of two tables. The 'current' table is the tickets that have not been billed out to the customer (no invoice number assigned and tickets not mailed). The 'archived' table has been billed out to the customer (invoice number assigned and tickets mailed to customer). The two select queries actually pulls from about 4 different tables (customer table, job table, detail table, product table, supplier table) besides the two tickets tables which are tblDataTickets & tblDataTicketsArchive (tables are identical except for the name)

I think what the problem is, is that when I modified the names to make them more descriptive, I may have made them too long, so the sql Select statement will no longer fit in the code behind form. I may just have to put it in the queries in the database itself..even if I don't want to! I've not figured out how to to make them multi-line yet :/

wasn't going to, since it's so long, but here it is anyway..the select statement:

SELECT tblDataCustomer.strCustName, tblDataJob.guidJobNo, tblDataJob.strJobDes1, tblDataJob.strJobDes2, tblDataJob.strJobDes3, tblDataProducts.strProdName, tblDataSuppliers.strSupplierName, tblDataTicket.guidTKNO, tblDataTicket.guidDRID, tblDataDriver.strNAME, tblDataTicket.datTicketPost, tblDataTicket.datTicketHaul, tblDataTicket.datTicketLoad, tblDataTicket.strTicketNo, tblDataTicket.dblTicketQuan, tblDataTicket.guidPER, tblDataTicket.curHaul, tblDataTicket.strNotes, tblDataTicket.strAutomated, tblDataTicket.dblInvoiceNo, tblDataTicket.datInvoiceDate FROM tblDataSuppliers INNER JOIN (tblDataProducts INNER JOIN ((tblDataCustomer INNER JOIN tblDataJob ON tblDataCustomer.guidCustID = tblDataJob.guidCustID) INNER JOIN (tblDataDriver INNER JOIN (tblDataDetail INNER JOIN tblDataTicket ON tblDataDetail.guidDetailID = tblDataTicket.guidDetailID) ON tblDataDriver.guidDRID = tblDataTicket.guidDRID) ON tblDataJob.guidJobNo = tblDataDetail.guidJobNo) ON tblDataProducts.guidProdID = tblDataDetail.guidProdID) ON tblDataSuppliers.guidSupplierID = tblDataProducts.guidSupplierID WHERE (((tblDataTicket.strTicketNo)=[Forms]![frmSearch]![txtSearch] Or [Forms]![frmSearch]![txtSearch] Is Null));