Auto refresh a form

[missinglinq]

The last time I did that my Snaggle-toothed, Bow-legged, Bully Bitch jumped up in my lap, to get to the beef, knocked the plate over, and got gravy all over the keyboard!

 

[boblarson]

Keels:

The first thing you need to do is to GET RID OF THE LOOKUPS at TABLE LEVEL. You currently have several like this:

READ THIS to see more about why you don't want to use lookups at table level:
http://www.mvps.org/access/lookupfields.htm

Second, do NOT use * in field names. Do NOT, DO NOT, DO NOT! (did I make my point?) You should avoid special characters in field names but using the asterisk in a field name can totally cause way more hassle than its worth since the asterisk has special meaning in Access as a WILDCARD character.

(I will post more in a bit)

 

[boblarson]

Now, can you explain why you are only using the FIRST name of people? How do you know which person is which? Or does it matter. Is the name just for being able to address someone when picking them up?

 

[ctags009]

Hi Bob - I, too, used lookup fields to build all of the relationships in my db... Is there a VBA I can use to cycle through every field of every table to remove these? After reading what you posted with that link above I noticed that I was having some of the problems that were mentioned, but couldn't figure out where I went wrong

Basically, how can I run (and perhaps this is something Keels would want to do as well) something like this:

Dim db as Dao.database, rst as Dao.recordset
Set db = currentdb()

Then I suppose I would have to have something that finds-

all of the table names:

Dim GetTableNames As String(), strTableNames() As String, lngLoop As Long

ReDim strTableNames(db.TableDefs.Count - 1)
     For lngLoop = 0 To db.TableDefs.Count - 1
          strTableNames(lngLoop) = db.TableDefs(lngLoop).Name
     Next lngLoop

     GetTableNames = strTableNames

and all of the field names
**I'm not sure how to create a loop here**

Dim fld As Field
Set Rst = db.OpenRecordset(GetTableNames)
    
    For Each fld In Rst.Fields
        rst.fields(fld.name).Properties("DisplayControl") = 109
    Next
    Rst.Close

I feel like I have the pieces and I'm missing the glue...
(or I can be completely wrong all together (more likely))

 

[spikepl]

#24 Sometimes the urge to write code can overwhelm common sense. Walk through all your tables in design view and un-lookup the fields. That will not take long, and you'll know it's right

 

[smig]

Welcom Keels

Refresh will refresh the records = save any unsaved data.
Requery will requery the object you want.

Though they sound as doing similar things they don't.

I didn't want to go into too much details here, only to point out that the two should not be confused.

 

[keels]

The last time I did that my Snaggle-toothed, Bow-legged, Bully Bitch jumped up in my lap, to get to the beef, knocked the plate over, and got gravy all over the keyboard!

Hahahahaha

 

[vbaInet]

The last time I did that my Snaggle-toothed, Bow-legged, Bully Bitch jumped up in my lap, to get to the beef, knocked the plate over, and got gravy all over the keyboard!

Luckily I don't have a Snaggle-toothed, Bow-legged, Bully Bitch so my gravy was safe and well used

 

[keels]

Now, can you explain why you are only using the FIRST name of people? How do you know which person is which? Or does it matter. Is the name just for being able to address someone when picking them up?

No, we may get more than 1 Bob say, so if this particular Bob has called before then if I can list all people Named Bob it would be easier than trawling through heaps of names. You can usually Identify a passenger by their pick up address or destination so If 10 Bob's called am sure they would all be calling from a different pick up address or going to a different destination. And as long as the name, pick up add, and dest are all visible it would make life easier.

Cheers

 

[keels]

Keels:

The first thing you need to do is to GET RID OF THE LOOKUPS at TABLE LEVEL. You currently have several like this:

READ THIS to see more about why you don't want to use lookups at table level:
http://www.mvps.org/access/lookupfields.htm

Second, do NOT use * in field names. Do NOT, DO NOT, DO NOT! (did I make my point?) You should avoid special characters in field names but using the asterisk in a field name can totally cause way more hassle than its worth since the asterisk has special meaning in Access as a WILDCARD character.

(I will post more in a bit)

Yes SIR.

Lol hahahaha ok am learning, am listening. Well I am when I stop laughing, Bob has anyone ever told you your mad? But that makes two of us so carry on. OK so don't use * in field names ok didn't know that erm ok I can change that for something else. Will read why not to use look ups at table level but it means that The table is gonna be a very big table.

Cheers Bob

 

[keels]

Ok so it will bloat the db and cause some errors, ok. If I remove the look ups from the bookings table can I still use them on the main form?

Cheers

 

[vbaInet]

In place of the Lookups you can use Combo boxes or subforms.

 

[boblarson]

The table is gonna be a very big table.

No, you misunderstood me. You do not have the combo boxes defined at table level. You still have the lookup tables which have the values but you use the combo's on the FORM, not in the table.

 

[boblarson]

No, we may get more than 1 Bob say, so if this particular Bob has called before then if I can list all people Named Bob it would be easier than trawling through heaps of names. You can usually Identify a passenger by their pick up address or destination so If 10 Bob's called am sure they would all be calling from a different pick up address or going to a different destination. And as long as the name, pick up add, and dest are all visible it would make life easier.

I think we should re-examine the table structure and what data you are storing. The current structure you have is not optimal for what you are intending to have there. I sent a PM to Access MVP Paul Baldarelli (pbaldy) to hopefully come here and help you with your structure since I believe he's done up something like this before.

 

[keels]

I think we should re-examine the table structure and what data you are storing. The current structure you have is not optimal for what you are intending to have there. I sent a PM to Access MVP Paul Baldarelli (pbaldy) to hopefully come here and help you with your structure since I believe he's done up something like this before.

Ok Thanks Bob.

 

[vbaInet]

Access MVP Paul Baldarelli (pbaldy)

Now I didn't know that is Paul's full name

 

[keels]

No, you misunderstood me. You do not have the combo boxes defined at table level. You still have the lookup tables which have the values but you use the combo's on the FORM, not in the table.

Ok Bob I have made a second order table (booking table 2) and have the info defined as text boxes but I notice I can not see the passenger names on the table as these are all ID numbers and without a look up at table level I don't get what I need. Is there a way of showing the actual name of the passenger as oppose to the passenger ID without using a look up? The form part is fine but if I want to print a report of the bookings table I need to see Names addresses and such like not ID numbers which mean nothing on a report.

 

[boblarson]

Ok Bob I have made a second order table (booking table 2) and have the info defined as text boxes but I notice I can not see the passenger names on the table as these are all ID numbers and without a look up at table level I don't get what I need. Is there a way of showing the actual name of the passenger as oppose to the passenger ID without using a look up? The form part is fine but if I want to print a report of the bookings table I need to see Names addresses and such like not ID numbers which mean nothing on a report.

Okay, let's start from scratch. The way this should go is (simplified version since I don't have the ability to get into your database at work as I only have 2003 here).

tblPassengers
PassengerID - Autonumber (PK)
FirstName - Text
LastName - Text
This needs some other field to identify the correct
Person if duplicate names could exist. I usually use their own address.

tblBookings
BookingID - Autonumber (PK)
PassengerID - Long Integer (FK)
DestinationID - Long Integer (FK)
DateBooked - Date/Time
DateOfPickup - Date/Time
not sure about the other fields at the moment.

So for the bookings table I store the passenger ID but I use a COMBO BOX on the form to pull all of the passengers listed in the passengers table and have their names but store the ID.

 

[keels]

Okay, let's start from scratch. The way this should go is (simplified version since I don't have the ability to get into your database at work as I only have 2003 here).

tblPassengers
PassengerID - Autonumber (PK)
FirstName - Text
LastName - Text
This needs some other field to identify the correct
Person if duplicate names could exist. I usually use their own address.

tblBookings
BookingID - Autonumber (PK)
PassengerID - Long Integer (FK)
DestinationID - Long Integer (FK)
DateBooked - Date/Time
DateOfPickup - Date/Time
not sure about the other fields at the moment.

So for the bookings table I store the passenger ID but I use a COMBO BOX on the form to pull all of the passengers listed in the passengers table and have their names but store the ID.

Hmmmm ok here's the thing. Most of our passengers are random passengers so we may not get an actual address for them just a pick up point they may also have more than 20 pick up points and destinations. I would also need to have their details before they book to populate the table this is not feasible because the bookings are so random and your talking about 7 -8 hundred different passengers over the course of a yr each taking on average 4-5 cabs a week. Storing passengers names for this purpose is not needed and duplicate names are ok because there are multiple variables to define who they are. Put simply I need a simple db but one that I can query accurately to get info if I need it.
So my plan is something like this:

Name table, to book random names.
Address table to book to and from anywhere in London.
Drivers detail table
Postcode table
Account table
Notes table

Now the Bookings table will hold records of each job and but I would like to be able to query the table from the form and pull up duplicate records for one name, the name that is entered on the booking form.
Can this be done?