Check Database from Excel to Access 2010

[Mohanss82]

I am comparing our Records vs Bank records where I want the Cheque no & Cheque Amt from Check Database to be retrieved for comparing. Please suggest me a way around.


Attached is my Union Query for comparison:

SELECT [tbl_DFSI Weekly Cheques].[Contract Number], [tbl_DFSI Weekly Cheques].[EMI Date] from [tbl_DFSI Weekly Cheques]
UNION SELECT [tbl_Citi Weekly Banking MIS].[ContractNo], [tbl_Citi Weekly Banking MIS].[ChequeDate] from [tbl_Citi Weekly Banking MIS];

Relationship:

[June7]

Why don't you build a query that joins tables on the [Cheque Number] and [ChequeNo] fields?

[Mohanss82]

Can you help me on this ?

[June7]

How? What do you not understand about building a query that joins two tables on common data? Use query builder.

[Mohanss82]

Hi. I created an Query to get the matching results between our Comparison table and Check database. Based on which I tried to compare Ours vs Bank, where I am getting this below error msg again. Please help.

[June7]

I don't understand how query builder would generate that error. Never seen it before. Why would Visual Basic be involved? Why would there be a custom function involved?

[Mohanss82]

Hi,

I am trying to compile a Report using a Access form by entering multiple primary keys values separated using comma, so that at the command of a button,a Report should be exported for those values. otherwise we will have to look out manually for 100 such records daily. Please suggest.

[June7]

Would have to analyze the VBA code to debug the error.

For multiple parameters on one field, review http://allenbrowne.com/ser-50.html

[Mohanss82]

Hi All,

I still require help on below 2 important stuffs:

1. I am trying to compile a Report from a Access form by entering values separated using comma and at the click of a button,a Report should be exported for those values. otherwise we will have to look out manually for 100 such records daily. Please suggest.

2. I want to update the status for selected records by using records selectors, where I will select few and change the Cheque status list box from the form header. Please help.


Attached Images

[June7]

1. VBA code to construct criteria string that uses IN operator. Is the field a number or text type? Otherwise, code to parse the CSV string and recombine with OR operators. There may be a limit on number of OR operators in an SQL WHERE clause - there is a limit of 99 AND operators.

2. Run an SQL UPDATE action. I see a combobox, not a listbox.

[Mohanss82]

Thanks.

Will try on the same and will get back to you.

I created this as an desktop app, but I wanted this to create/host in a web db. Is that possible just sharing in a url or through any other way ?

[June7]

I have never built web app but AFAIK, just giving someone a url will not accomplish sharing, except maybe to download a file. Common topic in forum. Here is one https://www.accessforums.net/sharepo...ess-50975.html

[Mohanss82]

I tried few things and couldn’t find any update or clue on my requirements yet. Require help on the below and much appreciated.
1. On a Daily Basis, I have to select few records manually from the database separately every time and Paste them in to an excel file and email them. Now I am trying to build a Report using an Access form by entering multiple identifiers (primary key values) separated using comma, so that at the command of a button, a Report should be exported for those values alone. Please suggest.

2. I want to update the status for selected records by using records selectors in a split form, where I will select few and change the Cheque status combo box from the form header. Please help. Refer to the Image of the Form shared on post No 69 dated 05-28-2015, 10:23 PM.

[June7]

Here is one option for item 1 http://allenbrowne.com/ser-50.html

For item 2, I just tried to do something like what you describe. I selected records, then clicked a button. The selection was retained only if I used shift and click to select records, dragging mouse down the record selectors didn't work. Code can then loop through the selection and do something with them. Something like this:

Private Sub StatusEdit_Click()
Dim N As Integer
Dim intHeight As Integer    'stores value for number of records selected
Dim intTop As Integer   'stores value for position of the first selected record in recordset
intHeight = Me.SelHeight
intTop = Me.SelTop
With Me.RecordsetClone
If intHeight < 1 Then
    MsgBox "No records have been selected."""
Else
    For N = 1 To intHeight
        .AbsolutePosition = intTop - 1    'AbsolutePosition property is 0 based counter so must -1 to get position within the recordset
        .MoveFirst
        While Not .EOF
            'do something with the record, like:
            .Status="something"
            .MoveNext
        Wend
    Next
End If
End With
End Sub