Help for a newbie on making forms 3

[muras]

Hello everyone!
I´m a newbie in microsoft access and I would relly be greatfull if you guys could give me a little help .
Here is the situation:
I have 2 tables in the database:
- The first one have 1 field(StationName)
- The second one have 10 fields(Localization,Instrument_in_Station,condition,price,...)
My question is:
-How can I do a form that when I put the Station name and click on a search button it could shows me the reffered information(shows me all the information on the second table reffered to the stationName)?

Thanks in advance for every help that you could give

 

[Remou]

This looks like a form and subform set up. Try creating a form from the first table and then adding a subform, making sure that the wizard is clicked. Follow through the steps and it should link the subform to the main form so that when you move through the main form records the subform shows related records. Is this close to what you want?

 

[lars7]

Hi,

You need a link between the tables, like StationName and StationNameID in the first table and stationNameID in the second.

 

[muras]

Hi!
Thanks,Remou and lars7, for the quick reply

Well, here is the situation:
I can see the form with all the information(already made the relationships between the tables), but how can I make it more resumed,like making a field that I could put the station name
and it will search the database for the station and shows the information reffered to it.I guess it envolves making a search button that must have some type of visual basic code.
What do you think guys?

Thanks

 

[Remou]

You can also do this with a wizard. Add a combobox to the main form and follow through the steps, choosing "find a record based on my selection".

 

[muras]

Remou, i´ve choose the combo box, but the wizard does not offer the "find a record based on my selection",how can I solve this?

Thanks

 

[Remou]

You must have a bound form, that is, the main form must have the Recordset property set to a table, query or SQL string.

I was half asleep when I answered you. It now seems to me that what you have is a table with ten fields and a look-up table, StationName.

[tt]tblStationNames
StationNameID -> Primary Key
StationName

tblStations
StationsID -> PrimaryKey
StationNameID
<...>[/tt]

However, I cannot see why you would need two separate tables in such a set-up as I can only see a one-to-one relationship between these two tables. It is important to read

http://www.r937.com/relational.html,

if you want a solid design for your database.

 

[muras]

Hi,
I´ve made a comboBox with the name of the stations,but when I choose one of those the information does not refresh ,is there any way to solve this?

Thanks

 

[Remou]

Did you use the wizard? What do you mean by refresh?

 

[muras]

Hi,
yes,i used the wizzard,but let me expose the situation so may you can solve it:
-I have a form with all the fields of the two tables
-The relantionship between the two tables is working,I have 60 station names that have associated information to them,till here everything is working great.
-The next step was to make a combo box,with the wizard I chosse the table with the station name ,doing this I had a box with all the 60 name stations.
-My main goal was by chossing a station name from the combo box it automatic should appear the information related to that station,but thats not hapening,the combo box don,t do nothing but to show the station names,I was looking to when I choose a station it will show me its individual information of it

Hope this could help

 

[Remou]

Did you choose "Find a record on my form ..." when you were creating the combo with the wizard? Is there any code for the form? If there is, please post it. What is the Record Source of your form? What is the Row Source of your combo?

 

[lars7]

hi again Muras

in the forms recordsource sql you could try this place this bit of code in the station column with the names you have give your form and your combo box:

[forms]![yourform]![YourCombo]

and in the after update of the combo box try this:

docmd.requery

if this doesn't work could you please post your Sql and maybe it would be easier to help you.

 

[muras]

Hi Lars and Remou,

Remou,yes I choose "Find a record on my form"but there´s no code associated

Sorry guys I´m relly a noob on this,but what I´m trying to say is that I would like to make a search button that when putting the station name and click it it will retrieved the information associated to it and I don´t know how to do it

By the way, is there a way that I can put or upload an image of what I get here in the forum, it will be lot easier to show you guys my problem as I will post the form and what I´m getting

Thank you both

 

[Remou]

It is not permitted. If you have a website, you can put your database there. It would be a good idea to read this:

http://support.microsoft.com/kb/287658

 

[AccessGuruCarl]

Hello muras,

Here is what your looking for I think.

On your form with the 10 fields...
When you open it, it displays 1 of xxxxx
Basically all the records!

** Add a Form Header/Footer Section

Make sure the wizard button is pressed. And add a combobox to the Form Header section.

Choose the default - Lookup value in table or query, click next.

Select the table that has the 1 field(StationName)

Click Next - Then move that field over to the right.
Click Next again, You should now see a list of the 60 stations.
Click Next, and choose remember this value for later use.
Click Next - Give the 'Label' a Name - Select Station to View - Click Finish

Open the properties to the new combobox.
Rename it to: cboStationLookUp
On the Event Tab: DoubleClick inside the AfterUpdate
It should fill in this: [Event Procedure]
Now click the elipse button(...) to open the code window.
Paste this code:

Private Sub cboStationLookUp_AfterUpdate()
Dim strSQL As String
strSQL = "Select * From YourTable Where StationName='" & Me.cboStationLookUp.Value & "'"
Form.RecordSource = strSQL
Me.Refresh
End Sub

Private Sub Form_Open(Cancel As Integer)
Me.cboStationLookUp.Value = Me.StationName
End Sub

Save the form.
Close it, and re-open.
When you select a station from the combobox, the form will update to the current selection.

If your form also has a combo for the station, and you want to change that to a regular textbox, in design mode, select it, Then choose Format from the Menubar, ChangeTo - Textbox
This will prevent a user from changing the station by mistake if they use that combo, and do not reset it back before closing the form.

Hope this was what your looking for, or please explain in better detail.




AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[AccessGuruCarl]

muras

I forgot to mention..
If you just want to filter the form.

Use this in the update event instead

Me.Filter = "StationName = '" & Me.cboStationLookUp.Value & "'"
Me.FilterOn = True

Me.Refresh

This will activate the filter button, and allow you to remove the filter to view all records again simply by clicking the Remove Filter - Looks like a funnel(F.Y.I.)

Have Fun....

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[AccessGuruCarl]

If you would like to see a sample of this:
Download this file:

http://24.24.47.186/beta/biblio2k.mdb

Take a look at the Title Form
One combo uses the filter
the other uses the recordsource.

This file is sitting on a beta site I use to test things.
I host the site, but it's not a Static IP - It changes...., and my ISP changes the IP almost weekly, sometimes daily depending on how much traffic it's getting. So download it quick if you want it.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[muras]

Hi,
Thanks a lot 'AccessGuruCarl' you solved my problem
Thank you all the guys that helped me in this one