Button locked and Text Suggestion (2 Viewers)

[carlosdpr]

Hello all!

I did a basic search about this issue but I couldn't find anything related.

So, is it possible to create a button that is locked until all the fields (text boxes for example) are filled, to make sure that all the fields are completed before "confirmation".

Another question, is that if it is possible to when I am entering data in a field (like a name of a city for example), as I am typing it would show suggestions of the other city names I have already put. This would not allow the users of the database to put the same city with different names (like abbreviations).

Thanks in advance!

 

[sneuberg]

For your first question you could start by disabling the button in the Form Load event

Me.TheNameOfTheButton.Enabled = False

Then create a function in the form's module that checks all of the text boxes and returns true if they are complete something like:

Private Function IsComplete() As Boolean

If Len(Me.txtText1 & vbNullString) > 0 And _
    Len(Me.txtText2 & vbNullString) > 0 And _
    Len(Me.txtText3 & vbNullString) > 0 Then
    IsComplete = True
Else
    IsComplete = False
End If

End Function

Then in each of the afterupdate events of the text boxes put:

If IsComplete Then
    Me.TheNameOfTheButton.Enabled = True
End If

 

[sneuberg]

Concerning your second question you might try making the field's control a combo box with the row source of it being the distinct values of the field. Let's say the form was bound to a table named Table1 and the field was City then the row source would be:

SELECT DISTINCT City FROM Table1;

You will probably want to do a save and requery the combo box in the afterupdate of the combo box so that anything you add shows up right away.

 

[carlosdpr]

Concerning your second question you might try making the field's control a combo box with the row source of it being the distinct values of the field. Let's say the form was bound to a table named Table1 and the field was City then the row source would be:

SELECT DISTINCT City FROM Table1;

You will probably want to do a save and requery the combo box in the afterupdate of the combo box so that anything you add shows up right away.

Hello, thank you for the fast answers!
I have some trouble understanding this answer. Should I do it with a query or something?
Imagine I have a table with some cities, and then a Form from another table where I need to put a city where a person lives. This is what I have done: In this Form I have the text box that is the "city_id". First I have changed it to a combo box, then in the row source I have put table_city (so row source Type is Table/Query) and then in the format tab I have changed the column counts to 2 and the column widths to 0cm;4cm (so that I can see only the name of the city and not the id number).
The problem with this is I can't add new cities when I am in the form, I can only choose the ones that I have already put. I can only add a new city by going to the table for example.
What I want, if possible, is to be able to choose from a list that I already have (like in a combo box) and if the city is not there add it. Is this possible?

If what you said can make this happen, I would be very happy if you could please explain me better this parts: "row source of it being the distinct values of the field" and "do a save and requery the combo box in the afterupdate of the combo box so that anything you add shows up right away"

Regarding the other answer, it worked but with some problems. If the fields are not all text boxes does something change in this line?:
If Len(Me.txtText1 & vbNullString) > 0

Because some of the fields are combo boxes (for example) and I think it assumes that they are already filled...


Thanks in advance!

 

[sneuberg]

Regarding the other answer, it worked but with some problems. If the fields are not all text boxes does something change in this line?:
If Len(Me.txtText1 & vbNullString) > 0

Because some of the fields are combo boxes (for example) and I think it assumes that they are already filled...

Thanks in advance!

It should make any difference unless the combo boxes have some default value other than an empty string. But if they did you would see it. If you upload your database I'll see if I can figure out what's going wrong.

 

[sneuberg]

What I want, if possible, is to be able to choose from a list that I already have (like in a combo box) and if the city is not there add it. Is this possible?

EDITED: IGNORE CODE IN THIS SEE NEXT POST

The way I was suggesting to do this didn't require an additional table for the cities, but since you already have one there is an advantage to it in that you don't have to save records in your combo after update. Sometime that causes problems.

So with the separate cities table what you want to do is update this cities table with the new city whenever someone adds a new city in the main form. So in the afterupdate of the cities' combo box you will want code like:

'Check to make sure city isn't already been added
If DCount("*", "[the name of the cities table]", "[field name of city] = '" & Me.NameCitiesCombo.Column(1) & "'") = 0 Then
    'add new city to cities table
    CurrentDb.Execute "Update [the name of the cities table] SET [field name of city] = '" & Me.NameCitiesCombo.Column(1) & "'"
    Me.NameCitiesCombo.Requery
End If

Where you will have to substitute the names in this code for the ones you have. Note that the column 1 in NameCitiesCombo.Column(1) assumes the city name is in the second column. The city_id is probably the first.

For more info on DCount see http://www.techonthenet.com/access/functions/domain/dcount.php

For update statements see: https://msdn.microsoft.com/en-us/library/bb221186(v=office.12).aspx

To learn more about concatenating controls into the SQL see: https://msdn.microsoft.com/en-us/library/dd627355(v=office.12).aspx

 

[sneuberg]

Sorry about my brain fart. Obviously an insert is what's needed. The code should be like.

'Check to make sure city isn't already been added
If DCount("*", "[the name of the cities table]", "[field name of city] = '" & Me.NameCitiesCombo.Column(1) & "'") = 0 Then
    'add new city to cities table
    CurrentDb.Execute "INSERT INTO [the name of the cities table] ([field name of city]) VALUES ( '" & Me.NameCitiesCombo.Column(1) & "')"
    Me.NameCitiesCombo.Requery
End If

For more info on INSERT INTO see: https://msdn.microsoft.com/en-us/library/bb243852(v=office.12).aspx

 

[carlosdpr]

Thank you again.

I have put the code with the right names in the afterupdate of the cities combo but the problem persists. When I put some random name that is not in the cities table the error "the text you entered isn't an item in the list".

I will send you the database if you want to take a look. It is mostly in portuguese but I will try to guide you.
The table with the cities is (t_cidade_origem), the combobox is in the form (F_peregrino) and the combobox is the one in front of ("Cidade de Origem"). So basically I want to either choose one of the cities that is in the combo or if it is not there simple write the new city name in the combo and that new city would then be added automatically to the table with the cities. If this is not possible maybe you know another good solution?

Regarding the "Lock Button" problem, you can see it in the form (F_carimbo). The button is locked in the beggining, but after I put the first 3 fields in, it unlocks.

Thanks in advance!

EDIT: How can I send you the database? I can't post a link because I don't have 10 posts yet. The size is 2,5MB...bigger than 2MB. Can't I PM you with the link or something?

PS: I tried the (SELECT DISTINCT...) thing in the combo in front of ("Local de Inicio") in the form (F_peregrino) but the list appears all blank...Although when I have inserted the SELECT DISTINCT...in the row source and then made Run it returned the results that I wanted...

 

[sneuberg]

Thank you again.

I have put the code with the right names in the afterupdate of the cities combo but the problem persists. When I put some random name that is not in the cities table the error "the text you entered isn't an item in the list".

Check in the combo properties, Data tab to see what Limit to List is set to. You want it set to No.

 

[carlosdpr]

Check in the combo properties, Data tab to see what Limit to List is set to. You want it set to No.

I tried that and this error appeared:
Microsoft Access can't set the LimitToList property to No right now.
The first visible column, which is determined by the ColumnWidths property, isn't equal to the bound column.
Adjust the ColumnWidths property first, and then set the LimitToList property.

So, should I change the boundcolumn to 2 to be bound to the city_name and not the city_id, can't these create some problems after?. Change the width of the city_id to a value bigger than 0 (I don't like this way because then in the combo it would appear the city_id too...) or do by other way?

If I can change the boundcolumn to 2 do I need to change of the combo like in the (Me.NameCitiesCombo.Column (1)) part?

Thank you again.

 

[sneuberg]

Sorry, forgot about that. My suggestion now is to get rid of the city_id altogether. I don't think I makes sense to have a Autonumber for a primary key in a simple single attribute table anyway. Make the city name the primary key of the cities table. You will have to change the field in the main table to text. If you have a lot of records already added let me know and I'll tell you how to update them from the number to the text with a query. Otherwise just update them manually.

The code would then be:

'Check to make sure city isn't already been added
If DCount("*", "[the name of the cities table]", "[field name of city] = '" & Me.NameCitiesCombo & "'") = 0 Then
    'add new city to cities table
    CurrentDb.Execute "INSERT INTO [the name of the cities table] ([field name of city]) VALUES ( '" & Me.NameCitiesCombo & "')"
    Me.NameCitiesCombo.Requery
End If

In the relationships you will want to enable casada updates. With the surrogate key (autonumber) this wasn't necessary but that benefit isn't worth the other hassles it presents.

 

[carlosdpr]

Hum...I don't think that my teacher would like that (the remove the city_id thing) but I can try to talk to him about that.
In the meantime, is there another solution?
If not, and you could tell me how to update the records, I already have like 200...

Thanks!

 

[sneuberg]

To update the main table with the city names:

1. Backup your database
2. Add a new field to the main table for the city name let's say CityName2, leave the City ID
3. In the Create TAB, Click on Query Design
4. With the Show Tables dialog add the main table and the cities table
5. If the city_id in the main table isn't already joined with the city_id in the cities table drag the city id from one to the other to join them
6. In the DESIGN tab, Query Type click on the Update ICON in the ribbon
7. Below in the grid select the newly added CityName2 for the field
8. For Update To add [the name of the cities table]![the field name for city name]
9. Click the Run Icon in the ribbon to run the query.
10. Close the query. You don't need to save it

Let's make a new table of cities for the combo box for this single attribute case so that you still have the one with the id in case you teacher kills this idea.

1. In the Create TAB, Click on Query Design
2. With the Show Tables dialog add the main table.
3. Double click on the city name to add it to the fields
4. In the DESIGN Tab, Query Types click on Make Table
5. The Make Table dialog enter a table name, maybe tblCity2
6. Change to SQL view (Right click on the query name and click SQL View)
7. After the SELECT keyword add the keyword DISTINCT .Make sure there's a space on both sides of DISTINCT
8. Change to Design View
9. Click on Run in the ribbon to run the query
10. Close the query. You don't need to save it

You can create a new combo box using the wizard and this table as the source and it should work fine. You will just need to change the control source to the new field in the main table.

 

[carlosdpr]

Hello!
This didn't work very well.
The problem is:
I have the table only for the cities (fields are city_id and city_name) that already have like 200 cities in there.
My main table still doesn't have any data in there, because it is supposed to store data about the people that want to make a reservation in the hotel (the fields in these table are identification, name, age, city_id, etc...), so I am supposed to add it when someone "hypothetically" wants to make a reservation.
When I tried to do your suggestion (I add the CityName2 to the main table), since I only have 2 rows of data in the main table it only updates those 2 rows with the city names...

I think that if I had a lot of data already in this main table (in other words, if I had data of a lot of costumers that already had made a reservation) I could have a lot of different cities already in that table and it would be a "good start" and then when I made a new reservation this cities of the people that have already come to the hotel would be there already.

Since I am doing this for a class but at the same time it is for a real inn (similar to hotel) of a friend of my teacher that will open soon, the database will be with no data in these main table. (I will only put data to test it and for the class, but for the inn owner it shouldn't have any data in these main table at least).

So, what I basically want is to have like a field to put a city of where the person is. I could do this easily by just putting the city as an attribute (that is, a new field) in the main table and as I am writing new data of a costumer, simple put the city name, like I please, in a text box. But, the owner of the inn doesn't like this idea because he wants to be able to make statistics and graphs with the data afterwards (for example, how many people come from a specific city) and with this easy solution (the simple text box) there is the problem I already talked about, same city with different names (because of abbreviations or errors when typing). And the better solution, at least in my mind, is to have already some of the most "important" or "known" cities stored and then as I (or someone) is registering a new client, the city field would show a suggestion (as I am typing) and if the city isn't stored in the database yet, I could just add it and then in a future reservation, that new city and the others that were already there would be in the suggestions.

A solution for this would be to have the combo with the cities I have already stored (the suggestion problem disappears) and if the city is not there, go to the table of the cities and add the new city there, and then update the combo with this new city. But this is not very user-friendly so I would like (if possible) to add the new city in the same form that I am doing the input of the data of the client.

Sorry for the trouble! I hope you understand what I want, I still hadn't the opportunity to talk to my teacher about this, maybe he has a solution!

If you have any ideas, share it please!
Thank you for your solutions anyway!

EDIT: I think I have found a good solution. If I create a new combo but instead of the row source type be (table/query) if I choose "Value List" and then I choose the option to store the value (in a new field like CityName in the main table) I think it could work!
With this solution I don't need the table with the cities. I just put all the cities that I have already in this value list, and I if the city is not there I could just simply add it. I didn't try this solution before because I thought that with this "type" of combo I couldn't do the suggestion thing. Do you think that this works well or some problem could occur?

 

[sneuberg]

Sorry I thought the 200 records you we talking about were in the main table. If that table is empty then we are back to what I was suggesting in post #11 which was just to get rid to the city_id field.

This new way you found might not sort the cities and of course would require you to enter the two hundred cities you already have. I've attached a simple database that demonstrates what I've been trying to get across. Please take a look at it to see if it helps you understand how this is suppose to work.

I add a function to the code to handle the case of single quotes in the city names. That's not likely but it has to be considered. The code is now.

Private Sub cboCity_AfterUpdate()

'Check to make sure city isn't already been added
If DCount("*", "[tblCity]", "[CityName] = '" & ESQ(Me.cboCity) & "'") = 0 Then
    'add new city to cities table
    CurrentDb.Execute "INSERT INTO [tblCity] ([CityName]) VALUES ( '" & ESQ(Me.cboCity) & "')"
    Me.cboCity.Requery
End If

End Sub

Private Function ESQ(str As String) As String

ESQ = Replace(str, "'", "''")

End Function

 

[carlosdpr]

It works very well! I will try to talk to my teacher too see what he says about it!

What did you think of my solution in the Edit part of my last post? Does it work or it would be problematic somehow?

Thanks again!

 

[carlosdpr]

I have another question.
Is it possible to in the city table have a country associated to the city and then in the form, create a new combo box where I can choose the country and then in the other combo it would only appear the cities of that specific country. I know this is possible.
The problem is, if the city is not there and I want to add it like I can do with the solution you posted, can I at the same time add the corresponding country and then the city table would be updated with the new city and the country.

Thanks in advance!

 

[sneuberg]

It looks like you can do this. I've updated the attached demo database to include a country and cascaded the combo boxes. I didn't add a table for the countries but just added the country to the cities table. So to get the country for the county combo box I use the DISTINCT keyword in the row source query which looks like:

SELECT DISTINCT tblCountryCity.CountryName
FROM tblCountryCity
ORDER BY tblCountryCity.CountryName;

The code to update the now tblCountryCity table has be change to

Private Sub cboCity_AfterUpdate()

'Check to make sure country/city isn't already been added
If Len(Me.cboCity & vbNullString) > 0 And Len(Me.cboCountry & vbNullString) > 0 Then
    If DCount("*", "[tblCountryCity]", "[CityName] = '" & ESQ(Me.cboCity) & "' And [CityName] = '" & ESQ(Me.cboCountry) & "'") = 0 Then
        'add new country and city to CountryCity  table
        CurrentDb.Execute "INSERT INTO [tblCountryCity] ([CountryName], [CityName]) VALUES ( '" & ESQ(Me.cboCountry) & "','" & ESQ(Me.cboCity) & "')"
        Me.cboCity.Requery
        Me.cboCountry.Requery
    End If
End If

End Sub

The thing to note here is that the update is not done if either of the combo boxes is blanks (nulls or empty strings) as you wouldn't want blanks added to the tblCountryCity table.

 

[carlosdpr]

Thanks! It works pretty well!