How to bound a text box

[bundy75]

Background: I have mysql 5.1 installed on my server as the back end of my database and trying to use ms access 2003 as a front end. I have been able to successfully connect to mysql and create forms using the wizard but when I try to create a form in design mode I can't seem to select a control source for a text box. So whenever I enter text in a textbox it doesn't update the database. I notice when using the wizard it asks which tables to use is there some way to set which tables your form will be using in design mode? All suggestions much appreciated.

[pbaldy]

The Record Source property of the form determines what table/query it is bound to. Once that is set, the Control Source of your textbox can be set to the appropriate field within that table/query.

[bundy75]

Thanks for the fast reply, that seems to work if I set the resource to one of my tables but as soon as I use multiply tables everything in my form disappears including labels?? Doesn't make sense the label is not even bound by anything??

[pbaldy]

When you use a query instead of a table, you run the risk of creating a read-only recordset:

http://allenbrowne.com/ser-61.html

Depending on your structure, you likely need subforms rather than one query with everything in it.

[bundy75]

Thanks Again, I have narrowed the problem down but a subform isn't working either.
I have a client table that contains a client_id INT and several other data types and I have a med_alert Table that has a med_alert_id INT and a med_alert VARCHAR(64) and I have a client_alert table which contains a client_id INT and a med_alert_id INT I was hoping to include these tables on my form. themed_alert table to list all possible alerts and the client_alert table to insert any alerts this client has??

Does that makes sense or am I going about it all wrong??

Many Thanks for your help.

[bundy75]

Ok feeling confused. Can someone tell me if I'm even on the right track please.
Plan:

Tables

Client (
Client_id (auto)
Name
Ethnic_id
Address_id
);

Ethnic (
Ethnic_id (Auto)
Ethnicity
);

Address(
Address_id (Auto)
Home Address
Phone
Cell
);

Now is it possible to have a form that has three tabs on it called Client, Ethnic,Address. And if I Enter values into some/all of these it will update my tables.

ie if I enter the name Bob under the client and thats all it will add a new client will the name bob and nothing for ethnic_id and address_id and if I then tab over to address and enter in an address it will create a new address entry and update the client bob to have a matching address_id?

Thanks

[bundy75]

Ok I think I have found what part of my problem is. The auto incremnet type from my mysql database tables are not being carried over to access and are being replaced as just a number not an auto number and since its a linked table I cannot change them

[bundy75]

PLEASE HELP!!!!!!!!!

The auto generated number is driving me insane!!!!!!!!

mysql is set to auto increment the client_id so of course on the frontend(ms access) it is unknown at the time of insertion, but I need that as I use it on the form to set the foriegn key of another table. Everything works fine if I manually set the Client_Id, but I really don't want to do this as I would a way of looking up a value that could be used as an id.

Is there a way of automatically setting a field on a form in ms access to the next number in a mysql table.?

[pbaldy]

If you force a save of the record you should get the number. Try one of these:

DoCmd.RunCommand acCmdSaveRecord
or
If Me.Dirty Then Me.Dirty = False

[bundy75]

You are the man!!!!!!!!!!!!!:d

[pbaldy]

Excellent! Glad we got it sorted out.