Pulling data from access table to access VBA form automatically (1 Viewer)

[balvinder]

Hi,

I am using MS office 2007. I'am in process of making one automated VBA user form for multiple users (5) using split database for smooth data entry in common shared drive.

Problem: I want to populate data from shared access table to VBA form of each user by name & date automatically one after one once they click on "SAVE" till the time all actionable entries for each user is not finished.
Post clicking on "SAVE" button newly added user form entries will be sent to back-end database in common shared drive.

But I'am not able to find it any suitable code for this activity.

above idea is somewhat based on dialor concept we generally use in BPO companies for calling purpose. But in this idea call will be done through landline phone but details will be picked through VBA form..

Any guiding help will be highly appreciated.

Thanks in advance..!!!
Balvinder rayat

 

[VilaRestal]

It sounds a like a normal data entry form. Do understand what I mean? Have I misunderstood something?

 

[balvinder]

Yes,

It's a VBA data entry form in MS-Access in which i'am looking to pick data automatically one after one using two conditions (Older bounced date should come first & higher amount policy number as well) from my database table called "Telecalling_database" post clicking on "SAVE" button for current record. I have tried using DMin function but unable to add both conditions in one formula.

I have few telecallers name in combo box on the basis of data should be filtered.

If auto dialing & stop of mobile number by clicking on some command button is possible then it would be like amazing.

Thanks..!!!

 

[VilaRestal]

Something like this code in the form load and the combobox update events:

Me.RecordSource = "SELECT TOP 1 * FROM Telecalling_database WHERE [Telecaller] = """ & cboTelecaller.Value & """ ORDER BY [Bounced Date] ASC, [Policy Number] DESC;"

I've guessed at the fields names and the name of the combobox. Hopefully you can adapt it to the field and variable names you have.

 

[balvinder]

Thanks for help..!!!

But still i have some doubt in my code. I have my primary key "Instrument Number" (kind of cheque number).

In one separate text box I'am trying to get that number by using your code so that i can retrieve other fields from other tables using this text box value. I have attached both your & mine example. Request you to please help me to find error.

Form_S_No is the text field in which i want to populate instrument number from telecalling_database

My Example

Me.Form_S_No = "SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ ORDER BY [Modal_Premium] DESC, [Bounce_date] ASC;"


Your Example

Me.RecordSource = "SELECT TOP 1 * FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ ORDER BY [Instrument_Number] ASC, [Modal_Premium] DESC;"


Thanks in advance..!!!

 

[VilaRestal]

"SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ ORDER BY [Modal_Premium] DESC, [Bounce_date] ASC;"

Is a query, so you can't directly assign a textbox to it. If you want the textbox to equal the Instrument_Number of the first record in that query then you would do this (neat trick):

Me.Form_S_No = CurrentDb.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ ORDER BY [Modal_Premium] DESC, [Bounce_date] ASC;")(0)

(The (0) at the end means the first element (zero-based) in the array that the Execute method returns)

I am not really understanding what you're trying to do but that code will assign the first instrument number in that query to the Form_S_No textbox

 

[balvinder]

Thanks a lot for help...

But with your code below error is populating...

Compile Error
Expected Function or variable

Error point stops at .execute part.

Private Sub Telecaller_Name_Change()

Me.Form_S_No = CurrentDb.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ ORDER BY [Modal_Premium] DESC, [Bounce_date] ASC;")(0)

End sub

 

[VilaRestal]

Oops my mistake. I forgot it should be
CurrentProject.AccessConnection.Execute
not
CurrentDb.Execute

(You'd think they work the same but they don't)
CurrentDb.Execute is fine for most things except this - returning the select statement as an array - it doesn't return anything but CurrentProject.AccessConnection.Execute does

 

[balvinder]

Thanks a ton !!!!

It's working...perfectly fine..

Small clarification..If i want to add one more condition in below statement then how should i go for it. e.g.

Condition: - I want to show records where Caller_Name in my database is blank. (I have added red code as per my understanding)

Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE [Caller_Name] = """ & Me.Telecaller_Name.Value & """ AND [Caller_Name] = NULL ORDER BY [Modal_Premium] DESC, [Bounce_date] ASC;")(0)

 

[VilaRestal]

AND [Caller_Name] IS NULL
or possibly if there are zero-length entries in Caller_Name:
AND Nz([Caller_Name],'') = ''
oh and part of the reason for not having spaces in fields names is to allow you not to have to use square brackets. So, this will work:

Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE Caller_Name = """ & Me.Telecaller_Name.Value & """ AND Caller_Name IS NULL ORDER BY Modal_Premium DESC, Bounce_date ASC;")(0)

 

[balvinder]

Thanks a ton VilaRestal !!!

You made my life today...

2nd option worked...God bless you...

Me.Form_S_No = CurrentProject.AccessConnection.Execute("SELECT Instrument_Number FROM Telecalling_database WHERE Caller_Name = """ & Me.Telecaller_Name.Value & """ AND Caller_Name IS NULL ORDER BY Modal_Premium DESC, Bounce_date ASC;")(0)

 

[VilaRestal]

You're very welcome and I hope it makes sense

The trick:

CurrentProject.AccessConnection.Execute("SELECT afield FROM anywhere WHERE anynumberofconditions ORDER BY anynumberoffields;")(0)

is much more versitile than DLookup, DMin, DMax, etc (especially considering 'anywhere' could be lots of tables joined in lots of different ways and replacing (0) with (9) would give the value of the field for the 10th-placed record in the query for example)
It's a very useful tool I think

 

[balvinder]

Sure...Will take care of this statement in future...

Thanks again..