Please help.!! (1 Viewer)

[adhishvellore]

Hi all,

Not sure if anyone could help me here. I have been assigned a project and have no prior experience working with Ms Access, but i need some database where i could manage my customer contracts and tariffs.

I did the simple part of creating the database, and the form template...but somehow i got stuck and lost the work that i had put on the database (by going through numerous videos and blogs).

I want a look up form where i can search based on certain criteria and then display the results. If there are multiple results based on my search criteria, then it should give me a msg to narrow down my search on fields where there are multiple options available.

I will start my search with the country, location, customer name and container size/type. Then i will select a date based on which it should validate with the "Valid To" date in my database and then the form should either display me the results of depot name, depot code, Carrier and teh amount and currency.

If there are no rates available for the customer name in the Contracts table, then it should pick the rate applicable from the tariff table (not sure if i am over complicating things here)

If anyone could pleaes please help me or guide me on what to do and suggest any tips and tricks it would be very valuable. I have my project deadline in a couple of days and have been using some other forums where i could not get timely or helpful tips. And besides the lost of more than half of the work did not really help at this stage.

Many many thanks in advance. Any questions please let me know.

I have attached a sample database that i was working on (in case required).

kind regards
adhish

 

[adhishvellore]

hi there,

i hv been able to re-construct my cascading filters now, but i do not know how to get the results of the search based on the different filters into the set text boxes

Does anyone have any query or code that i could re-use?

Please please...much appreciated. thanks.!!

 

[JHB]

Normally you are using an unbound (Main) form, (with combo-boxes or text-controls, used for selecting or inputting value to search for), with a sub form which shows the result from the search.
I've looked at you attached database, but I can't find out from where the values should come - you've to explain from where (table and field name) the list values in combo-boxes should come. (And I can see, you are using a bound main-form and no sub-form.)
You've also to explain in which table(s) and field(s) you are doing the search.
Maybe you should also attached you database again with you last changes.

 

[adhishvellore]

Hi there, thanks for the message.

All the values should be retrieved from the table PIO_Contract (if the customer name specified in the combo box exists in that table). If the customer name specified in the combo box does not exist in the table, then the values should be retrieved from the other table PIO_Tariff.

i have attached the database with the modifications (honestly not much progress other than the cascading filters). I am also not aware of how the subforms work as still going through a lot of reading to understand this.

Mnay thanks for your help.

Cheers
Adhish

 

[adhishvellore]

And now with the attachment

 

[JHB]

Fine, another question - how many search criteria (combo-boxes/textcontrol) do you want?
Now it is 4/(5) combo-boxes there has list values.
Country, location, customer name, container type/size, do you want more, (and if yes, which?) . Carrier has no list value.

All the values should be retrieved from the table PIO_Contract (if the customer name specified in the combo box exists in that table). If the customer name specified in the combo box does not exist in the table, then the values should be retrieved from the other table PIO_Tariff.

There are no customer name field in the table PIO_Tariff.

 

[adhishvellore]

Hi,
The search criteria can be limited to the below 4 fields.

Country, Location, Customer Name & Container Type/size

The values should be displayed from the PIO_Tariff table in case there is no match with the customer name mentioned or if there is no Customer name specified. Even if i could get the results based on search criteria on Country, Location & Container Type/Size...i would be happy

 

[JHB]

Så nu skulle den være ok - problemet er at de to tabeller ikke har de samme antal felter og samme feltnavne, derfor er det nødvendig at have 2 underformularer i hovedformularen, hvor de bliver synlige/usynlige efter behov.
Underformularene er placeret ovenpå hinanden, derfor kan du umiddelbart ikke se den anden.
Prøv den og se om den opfylder dine ønsker.

 

[adhishvellore]

hi, tusind tak for hjelpen
My danish is not so good yet, but that was very helpful indeed. exactly what i was looking for.!!

One small question - is it possible to have the subforms in a vertical manner than horizontal so that users do not have to use the scroll bar...it is not that important though, but just asking so that i can learn.

Many thanks once again and have a fantastic day ahead.!!

Cheers
Adhish

 

[JHB]

One small question - is it possible to have the subforms in a vertical manner than horizontal so that users do not have to use the scroll bar...it is not that important though, but just asking so that i can learn.

Many thanks once again and have a fantastic day ahead.!!

Cheers
Adhish

I'm not sure what you want - can you show it by an example, print screen or so?

Velbekom og i lige måde, (You're welcom, you also).

 

[adhishvellore]

Hi,
sure. what i meant was that if the results can also be in the same format as filters that is one below the other. For example:

Filters

Country : Germany
Location : Munich
Customer :
Cont Type/Size: 20'Dry

Results:

Depot Name: ABC Munich
Depot Code: DEMUN01
Amount: 50
Currency: Euro

Thanks

Adhish
Contract ID:

 

[JHB]

Do you only want these 4 as result?
But if you get more rows as result you need to scroll down to see all result, because
1. row
Depot Name: ABC Munich
Depot Code: DEMUN01
Amount: 50
Currency: Euro
2. row
Depot Name: result 2 row
Depot Code: result 2 row
Amount: result 2 row
Currency: result 2 row
3. row
Depot Name: result 3 row
Depot Code: result 3 row
Amount: result 3 row
Currency: result 3 row

 

[adhishvellore]

Hi Jørn,

Ideally i would like to have only one record displayed because otherwise it will still cause the users to pick the wrong record. But i understand that if we are doing this then i guess i will have to start assigning a primary key to my data tables whereby each record is unique and then it will be a combination of different fields. Is that correct?

Also, is it possible to convert this accdb file into a exe file so that i can give the exe to other users and keep the main database with myself?

Tusind tak for hjelpen.!!

mvh
Adhish

 

[JHB]

Ideally i would like to have only one record displayed because otherwise it will still cause the users to pick the wrong record. But i understand that if we are doing this then i guess i will have to start assigning a primary key to my data tables whereby each record is unique and then it will be a combination of different fields. Is that correct?

Hi Adhish.
Not quiet right - but I think you need a pointer/field which indicate which to show first, (primary contract).
You can't make an really execute file with MS-Access database, like VB-programs, but you can make a "runtime version". Search for Runtime+MS Access on the Internet.
Below is a description about the different file-types in MS-Access 2007/2010.

This section summarizes the file types that are used by the Office Access 2007 file format.

• .accdb This is the standard file name extension for databases in the Office Access 2007 file format. This format takes the place of the .mdb file name extension that is used in earlier versions of Access.
• .accde This is the file name extension for Office Access 2007 files that are compiled into an "execute only" file. This format takes the place of the .mde file name extension that is used in earlier versions of Access.

An .accde file is a "locked-down" version of the original .accdb file. If the .accdb file contains any Visual Basic for Applications (VBA) code, only the compiled code is included in the .accde file. As a result, the VBA code cannot be viewed or modified by the user. Also, users working with .accde files cannot make design changes to forms or reports. You create .accde files from .accdb files by doing the following:

1. In Office Access 2007, open the database that you want to save as an .accde file.
2. On the Database Tools tab, in the Database Tools group, click Make ACCDE.
   

1. In the Save As dialog box, browse to the folder in which you want to save the file, type a name for the file in the File name box, and then click Save.

• .accdt This is the file name extension for Access Database Templates.
• .accdr The .accdr file is a new file name extension that enables you to open a database in run-time mode. By changing a database's file name extension from .accdb to .accdr, you can create a "locked-down" version of your Office Access 2007 database. You can change the file name extension back to .accdb to restore full functionality to your database.

 

[adhishvellore]

ok..got it.!! thanks a lot.!!

hav en god dag.!