Adding records to filtered form.

[supa_sumo]

Ok heres the problem, i seem to have a little bit of a mind block at the moment so help would be greratly appreciated.

Basically there are two tables involved, tblBikes (which contains all information on the bikes) and tblHires (which contains all the information about hired bikes) these are linked with a one to many relationship as each bike can be hired out many times.
I have a button on a form containing bike information which opens the hires form filtered by the bike ID of the record selected in the first form. When I add a record to the hires form i need the bikeID to be automatically input into the field.

 

[Sammy B]

I would set my tables up this way:

BikeID
Bike_Name
Bike_Description
Bike_etc

BikeHireID
CustomerID
BikeID
Other_BikeHire_info

I would then hook them together as Bike info as main form with BikeHire as a subform with the BikeID as the connection. You could do the same for the customers.

Sam

 

[supa_sumo]

Ive already normalised my tables and have gone too far now to change it I only listed those two tables as they are the only ones involved, heres my table list if it helps.

 

[Sammy B]

Ok, it looks like you have a handle on the relationship thing. And it looks like you have done it correctly. Create a main form BikeID and any other info you would like from that table. Make the BikeID field a drop down and connect it to a subfrom tbHires with this BikeID field. You will then be allowed to add as many records to tbHires as you like and it will update it's BikeiD field to match the one in the main form as it goes along. The whole trick here is to use subforms.

Sam

 

[supa_sumo]

The problem is the information on the bikes is already in a subform as ive got a search thing in the form its difficult to describe but this is the layout of that from ive got many other complicated bits of code in this form and subform so i cant really change much without causing alot of hasstle.Heres the layout and the code im using to open the hires form might help you understand why ive gotta write it into the code somehow.

Private Sub cmdHires_Click()
Dim SearchStr3 As String
SearchStr3 = "[BikeID] = " & Forms![frmSearch]![frmSearchSub].Form![BikeID]
DoCmd.OpenForm "frmHires", acNormal
Forms!frmHires.Filter = SearchStr3
Forms!frmHires.FilterOn = True

 

[Sammy B]

From the form I see in the pic you sent there does not appear to be any subforms. You could put the hire subform beside the bike info and every time you went to a new bike record it would show all the historical hires for that bike. You could then record a new hire for that bike right in the subform.
If I am not making any sense then I suggest you zip up your file and send it to me and I will put the aforementioned subform in for you so that you can see how I would do it.

Sonofbc@gmail.com

 

[supa_sumo]

You make sence, the problem is i need to put the bike repairs in also using the same method and the form would look a lil cluttered with two subforms. Ill zip a copy of the database when i get home as i dont have a copy with me unless anyone has any ideas before then. Should be back next hour.

 

[Sammy B]

Yes, zip it up for me and I will do up an extra form with the data the way I see it in my minds eye.

Sam

 

[supa_sumo]

I sent it to that address but message sending failed is it deffinatly right? only on dialup and 499kb is alot to upload again

 

[Sammy B]

My addrss is
sonofbc@gmail.com

 

[supa_sumo]

It wont send, its as if the account doesnt exist, ive tried a few times and its too large to fin on forum, ill see if i can remember my website password n post it there unless u hav another email.

 

[supa_sumo]

Ok i have had difficulty sending the file due to the problems with email and a power cut but finally ive got a copy of the problem that is small enough to post. Any help would be very welcome as i was meant to finish it by today. You will find the file on the first post named db2

 

[Sammy B]

Ok, I have it. It is about 5:50 AM here and once I wake up I will do my best to get this done. Talk to you later.

Sam

 

[supa_sumo]

Any other help from anyone would be great as i really need to get this done like in the next few hours. I thought about maybe passing the bikeID of the selected record from the subform to a text box in the hires form, then when the save button is pressed the values could be input where required from the invisible unbound text box on the hires form. I have no idea how to code that tho and can not work it out and debug it in this short amount of time

 

[Sammy B]

I sent the Database to your hotmail account. The first one had a glitch but the second seems to work ok.

Sam

 

[Sammy B]

Revised Database

Simon,

Here is the database. Let me know how it works for you. I would have no problem adding a few features if you don't mind working with me. (I do not profess to be an expert or anything, I just like programming in Access)

Take care.
Sam

 

[supa_sumo]

That is sweet, i didnt think of that at all nice one. Im gunna start working on linking the database to a website to display all the bikes in the list with all details displayed and also a picture so im gunna have to add a new field to the table at some point for picture location. Im adding the Ledger reference Stock number and Frame number criteria boxes back as they are required by the staff. But other than that it does exactly what i wanted using anaternative method.
Thanks
Sci