autofillin code wrong?

[hugh0177]

Hi
When I enter one number in a field, I want that number to look up information in another table, and fill in some fields succesive fields.
I would prefer not to use a combo box because I am afriad I will hit the limit for number of characters that can be in a combo box.

Here is my code so far:

Private Sub SS_ID_Exit(Cancel As Integer)
Dim varSS_Title As Variant
varSS_Title = DLookup("SS_Title", "Sites", "SS_ID=[SS_ID]")
If (Not IsNull(varSS_Title)) Then Me![SS Title] = varSS_Title
End Sub

I basically copied what I saw elsewhere, and filled in my table and field names.

What is wrong? I think I may be putting the wrong things (tables, vs. fields) in the wrong places or something.

K

 

[Mile-O]

varSS_Title = DLookup("SS_Title", "Sites", "SS_ID = " & [SS_ID])

 

[hugh0177]

Thank you for a response:

I tried that and it didn't work

It said I have a syntax error.

This time, it pointed to the first line of code to debug. I don't know why, it was fine before.
Does it help to know that I am entering the information into this form as a subform?

More specifically about the code:
I am trying to enter a number (foreign key as [SS ID]) and get the title of an institution [SS title] to fill in the subform, organizations - sites (which was originally created as it own form).
The primary key I want it to lookup to is also [SS ID] and I want it to look up [SS title]. This is in the "Sites" form/table.

DOes this clarify what should be in the line of code?
in advace thank you soooooooo much for your help, it is very much appreciated
K

 

[Pat Hartman]

You don't need code at all. Create a query that joins your subform's table to the Sites table on SS_ID. Select the fields you need from BOTH tables. Then use this new query as the recordsource for your subform. All the fields you need to display will be available from the field list.

DLookup() is very inefficient. The query that joins to the lookup table is the way that relational databases are ment to work.

 

[hugh0177]

OK,

I looked how the properties of the subform and the fields of a subform are set up...

I would have to assign the record source to the entire subform right?

So I have some questions/points about that:

1) the subform is a form/table that is a connecting table in a middle of a many to many connection.

2) some of the fields in the subform are from yet another form (besides the subform and the sites form) , called the organizations form, that I was also hoping to get autofill in to work on.

3) More fields in the subform are unique and are found in neither of the connecting forms.

Wouldn't these three conditions prevent me from using a query as a record source because I would have to assign the entire subform to ONE record source.

Besides, if I did this, where would the new records be stored?
In the query? I would much prefer them to be in a table...they would have to be to fullfil the requirement of being a connection in a many to many connection correct?

If I have made some arong assumptions, please correct me.
thank you for you help in advance to whomever responds
K

 

[Pat Hartman]

You have some concept issues.
1. Data is stored in tables NOT forms. When you add a record or make a change to a field, the underlying table is updated. When the form is closed, any data sitting in unbound controls is lost.
2. Queries are simply a way of selecting data from one or more tables. They are bound to tables. That means that if you add a row to a query or change data in some column, the change actually happens to the underlying table.
3. Recordsets created by queries are not persistant. When the query closes, the recordset is destroyed.
4. Your form is probably bound directly to the table now. Look at the form's recordsource. All you need to do is change the table name to the name of the new query. As long as you have selected the correct columns, your form will continue to work just fine. You'll just have some new columns available in the field list that come from the "lookup" table.

 

[hugh0177]

OK
So I messed around with queries, and I found that some times when you enter information in them they save their information to an underlining table, and sometimes they don't. Why does this happen?

K

 

[hugh0177]

Sorry, I did not add this earlier, but in the query I created to work in the way you are suggesting, everything in the query saves to the underlining table EXCEPT the title of the site and the title of the organization, for which the purpose of this whole thing is.

The titles autofill in by themselves on the query, but DO NOT save to the table.

I am still doing something wrong?
K

 

[Pat Hartman]

Nor are they supposed to. That is the point of relational databases. Data is stored in ONE place. See how easy it was to retrieve that info? You don't really need to duplicate it. Access just pops it in as soon as you supply the foreign key

 

[hugh0177]

Hi

Thank you for your help so far. I know this is dragging on a lot longer than you had anticipated..., I hope you will continue to help me...

OK
so, I see what you are getting at, and I am glad that you offered to help, but the information you have offered me is NOT what I want.

Beauty of databases aside...
I realize that entering the information twice into two different tables is redundant
however, the purpose of entering the information twice is a question of userfriendlyness, not one of efficiency.
I am trying to make MY data entry more easy.
You see: IT has been decided that the information shall be in the table, not stored in a query, and viewable from a form.
Lets not go down that road...
So either you tell me a way that I can have the information automatically entered into the TABLE or, if impossible, or I can't figure it out, or I will spend millions of hours filling in these titles.

That is why I asked for code.
Thank you

 

[Pat Hartman]

but the information you have offered me is NOT what I want

- there's the conundrum. Do I load the bullet into your gun so you can shoot yourself in the foot? Not without trying one more time.

the purpose of entering the information twice is a question of userfriendlyness, not one of efficiency

You are using a form to store the duplicate value. Why would using a query as the form's recordsource be less user friendly? The form looks the same. You don't have to enter extra data.

IT has been decided

- sounds like a disembodied voice from the heavens saying, "THOU SHALT DO SOMETHING STUPID!!!"

There is no mystery to duplicating the data. Put the code that Mile posted into the AfterUpdate event of the SS_ID control. Make sure that the field names are changed to match your requirements. If SS_ID is a text field, you'll need to surround it with quotes:

varSS_Title = DLookup("SS_Title", "Sites", "SS_ID = '" & [SS_ID]) & "'"

 

[hugh0177]

Thank you for the info very very much.

I didn't mean to sound mean in the last post.

I meant to say it that way
"IT has been decided, that it SHALL be that way."
as a joke.

Like I said, while I have been hired to make this database, I do very little of the designing, and major decisions are not made by me.
How is filling out a table with information userfriendly? I don't know, ask my fifty five your old boss. Most of my superiors have a hard enough time opening a window.
So whatever...
Sometimes I fight these battles and sometimes I just do what I am told.

Don't tell me you have never met anyone in this position...

again, I'll see if it works, and if it does, thank you so much...
K

 

[Pat Hartman]

I have worked for lots of stupid people over the years and sometimes it is hard to refrain from pointing out their stupidity but of course, you need to work so you hold your tongue. Try showing the boss a datasheet style form. It looks like a table or a query but you have form and control level events that you can use to exersize some control over what is happening. Creating a form interface rather than a raw table/query interface is where you should be heading. Simple forms can be made using the wizards in a matter of minutes so there really is no excuse for using raw tables/queries for data entry. Use the switchboard manager to create a form that gives access to all your objects and hide the database container to disuade people from using it.

PS, I did realize that the "IT has been decided" was an exasperated reference to a pig-headed supervisor