autofill

[BoroLee]

I have a form which is not bound to a record source.

I have various queries which all contain the same field names, and these are the same field names as in the form.

i now want to be able to open the form based on any of these queries dependant on what button i press.

i.e. if i press A, the form will open with all the A records in it, if i click B, all the B records.

Any idea's???

 

[Mile-O]

You haven't made 26 queries - one for each letter of the alphabet?

 

[BoroLee]

I haven't yet, but i would have done. And 26 tables.

Help!!!!!

 

[Mile-O]

Three ways:

1) Look in Northwind database at how the Address Book works with one query and a macro.

2) Putting this in a module:

Public strLetter As String

Function Letter() As String
    Letter = strLetter
End Function

On the command button's on your form (for each letter) assign the letter selected to strLetter.

In a query, on the field you want, set the criteria to:

Like Letter() & "*"


3) A hidden textbox on your form

On the command button's on your form (for each letter) assign the letter selected to the textbox.

In a query, on the field you want, set the criteria to:

Like [Forms]![MyForm]![HiddenTextbox] & "*"


And change the names, of course.

 

[BoroLee]

This is what i currently have

 

[Mile-O]

This is what i currently have

Which one? I suggested three.

I fail to see where you are having a problem if you have already done one of these.

 

[BoroLee]

sorry.

am currently trying number 2.

what did u mean when you said assign the letter selected to strletter?????????

 

[BoroLee]

This is the code i currently have on each letter

Private Sub openA_Click()

DoCmd.OpenForm "Enter data", acNormal, "qryA", , acFormEdit, acWindowNormal
DoCmd.Maximize

End Sub

 

[Mile-O]

Okay, apologies for the ugliness of this example.

It does what you want with about 6 or 7 lines of code.

 

[Mile-O]

P.S. The query isn't used. I just never deleted it. It could, however, just be set as the RowSource of the listbox.

 

[BoroLee]

That doesn't exactly do wot i want.

if i create 26 tables, one for each letter of the alphabet, so that i can get id's like a1, b1, b2, e1, etc etc, i want to be able to click on a letter on the switchboard i.e. A, it will open the Standard form, but it will use the info saved in tblA and assign the next valid number i.e. a2.

If i click on B, the Standard form opens but looking at data in tblB and gives the next available number i.e. b3

 

[Mile-O]

if i create 26 tables, one for each letter of the alphabet, so that i can get id's like a1, b1, b2, e1, etc etc

There is NO way you need 26 tables.

If you are assigning like that then you just have to put a little bit of code in the BeforeUpdate() event of the form when you create a new ID.

i.e.

I see on another thread that the letter comes from a surname (the first letter)

So.

One query to split the ID field:

Letter: Left(IDField, 1)

and

Code: Mid(IDField, 2)

and the code for the BeforeUpdate() event

If Me.NewRecord Then
    MyNewID = Left(Surname, 1) & DMax("Code", "MyQuery", "Letter = '" & Left(Surname, 1) & "'") + 1
End If

 

[BoroLee]

ok.

By doing it this way though, will i end up with an a1,b1,c1,d1 etc etc etc?????

That is how they record things

 

[Mile-O]

Yep!

I'd still advise using an AutoNumber as the primary key and just indexing the Code field to No Duplicates.

 

[Mile-O]

And here's an example:

 

[BoroLee]

That is great. it works a treat.

The only thing i'm missing now is the CardID needs to be displayed asap.

can that be done????

 

[Mile-O]

The only thing i'm missing now is the CardID needs to be displayed asap.

can that be done????

See, better and easier to maintain than 26 tables and 26 queries.

As for your question: this is possible and only advisable if the database is used by one person at a time.

 

[BoroLee]

Yes, it would only be used by one person.

Can you tell me how please????

 

[Mile-O]

You can put the code in the form's BeforeUpdate() event into the Surname textbox's AfterUpdate() event instead. Take out the close form part though. As soon as you type the surname and move out of that textbox the CardID will be generated.

 

[BoroLee]

that doesn't seem to work