Display record on form by searching subform table

[CRBMartin]

Display record on form by serching subform table

I have a laptop database that tracks laptops and current laptopholders

the database is written in access 97

I have organised this in to a number of tables primarily Laptop and laptopholder
I have a form that is based on Laptop
I have a sub form based on laptopholder
the serial number is the primary key


my main form shows records based on the laptop table
my subform displays the latest occurrence of a loan for that laptop and the record selector on the sub form cycles through from latest loan to oldest loan of that particular laptop

I want to search through the laptop holder table using the laptopholders name rather then searching through the current records available on the subform, which are limited by the current laptop being displayed....if you follow that.

the reason I want to this is so I can get the laptop serial number that they currently hold. I could filter the query using IsNull in the returned date I want to further filter the query by name

to do this I thought to allow the database user to select a name from a drop down list or combo box oon the main form

then my query would return all users with that name who have a Null value in date returned (hopefully one)

I was then going to take the serial number and therefore the record number of the laptop and using this display that laptop on my main form....if this makes any sense

I don't know if this is the best way to do it or not but I want a button on my form that when they enter a user name the laptop the user is holding will be displayed and if not they get a message saying user does not hold a laptop at this time

this or anything similar would be fine

I have used a mix of macro's VBA and expressions to build this database

Mainly making headway by trail and error

 

[KenHigg]

Sounds like you may need another table, one that contains loan information. Do you currently have one of the following relationships

1. Many laptops to many laptopholders?
2. One laptop to many laptopholders?
3. One laptophold to many laptops?

 

[CRBMartin]

relationships

yes I have 1 laptopdetails to many laptopholders

I can post a list of my other tables/feilds/queries/forms/relationships etc if that will help or did my mad ramblings on my last post make some sort of sense

 

[KenHigg]

That may help. Is the db in production (are you using it), if so how many records are in it?

 

[CRBMartin]

laptop db has 63 entries under laptop details
and there are 120 occurences of loans in the laptopholder table

I have the following tables:

1. Laptop details
2. Laptop holder
3. Maintenance
4. A drives
5. CD drives
6. Printers
7. Internal user list
8. Software

relationships in place:
Laptop details to laptop holder: 1 to Many
Laptop details to Maintenance: 1 to Many
Laptop holder to A drives: Many to 1
Laptop holder to CD Drives: Many to 1
Laptop Holder to Printers: Many to 1
laptop holder to internal user list: Intermediate
Laptop details to software: 1 to 1

Laptop details loggs laptop specifications only
it has a auto number id(for some reason)
I set the primary key to the serial number

laptop holder tracks user info and date taken agreed return date and date returned

the peripheral devices tables conatin only a asset tag and info on make and model

I have a main form which has three sub forms:
the main form is based on laptop details
the first sub form is based on laptop holders and displays in a form layout with the latest entry first
I have another sub form maintenance which show in a data sheet view
I have a tick box subform for selecting software installed onthe laptop


phew will post this you need more info than this ?

 

[KenHigg]

Hum... Just a idea,

Looks like there seems to be some ambiguity in the 2nd table. What does each record represent? An occurence of a loan or the information about a person borrowing a laptop?

Seems there needs to be a split out of the user info. Say another table called tblUsers. (?) The main problem with putting user information in the existing table is that you can't be sure if user John Doe is the same John Doe in the other records (typos, etc)...

Your thoughts?

 

[CRBMartin]

Table Laptop holder

Serial Number : TEXT FK
Current location: Text
date Taken: Date/time
Agreed return Date : date Time
Open Return : yes/no
Date Returned: date/time
Further info: text
Laptop control form complete: yes/no
Name: text : combo box look up to internal user list
Contact details: text
Printer: text : combo look up to printer table linked on asset number
CD drive: text : combo look up to CD drive table linked on asset number
A drive: text : combo look up to A drive table linked on asset number



this table is only to see when the laptop was taken and by whom

contact details are filled in on the fly as agents may be out of town etc

 

[KenHigg]

Sorry for being log winded. The ambiguity, (from my perspective), is in the names of the tables.

As for your original need, wanting to drill down to a laptop currently on loan starting from a user name, I would do a pop up form based on on user names. Here you select a user name, say from a combo box. Then a subform would show all the users laptop borrows. Here youselect the open loan which would set the filter for the main form, and then the popup form would close itself. Make sense?

Also, should the 'laptop holder' / 'internal user list' tables have a 1 to 1 relationship?

 

[CRBMartin]

ok thanks...
I will try this but all topics there seem to be new to me so it will be a bit of an adventure

I will keep you posted on my progress

 

[CRBMartin]

ok I think its offical... I suck

Been trying all day to get my db to display the laptop held be a specific user

I just cant get my head around the problem

I have to leave work now but will be back to the grind in the morning

If anyone can shed some light on my issue before then I will be eternally greatful


My kingdom, my kingdom for a muse!

 

[bluetongue]

The information in http://members.iinet.net.au/~allenbrowne/ser-28.html may help with your problem.