VBA Code for textboxes that are on subform and insert correctly

[dwAccessUser32]

I have a form with a subform on it.

The form, frmPeople, has a subform for phone numbers. I want to set it up where the following phone numbers can be captured from textboxes without a combobox.

txtHome
txtWork
txtMobile
txtFax

The database has three tables that may be relevent:

tblPeople
PersonID

tblPhoneNumber
PhoneNumberID
PhoneNumberTypeID
PersonID
PhoneNumber


tluPhoneNumberType
PhoneNumberTypeID
PhoneNumberType


Does anyone have a suggestion on the best way to set this sort of code up. I am still relatively new to coding in MSAccess and would greatly appreciate any help.

 

[PHV]

If you want all phone numbers for a given person in a single textbox then have a look here:
faq701-4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dwAccessUser32]

Thank you for the help - I looked at the information, but I don't think it is quite what I need. It might be, but I don't think so.

However, I want four different textboxes and not a single textbox. Basically, I want to set it up where the form looks like this:

Home Number:
Work Number:
Mobile Number:
Fax Number:

And then have it where each of the values from the textbox is entered into tblPhoneNumbers. Each value needs to be tied to tblPeople (PersonID) and tluPhoneNumberType (PhoneNumberTypeID).

Any ideas on where to start with this? Again, thank you for any help.

 

[PHV]

So, you have a main form based on tblPeople and you want a subform showing all phone numbers of the selected person ?
The Recordsource of the continuous subform may be something like this:
SELECT N.PersonID, N.PhoneNumberID, T.PhoneNumberTypeID, T.PhoneNumberType, N.PhoneNumber
FROM tblPhoneNumber AS N INNER JOIN tluPhoneNumberType AS T ON N.PhoneNumberTypeID = T.PhoneNumberTypeID

This subform should be linked to the mainform on PersonID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dwAccessUser32]

The record source that you suggest,

SELECT N.PersonID, N.PhoneNumberID, T.PhoneNumberTypeID, T.PhoneNumberType, N.PhoneNumber FROM tblPhoneNumber AS INNER JOIN tluPhoneNumberType AS T ON N.PhoneNumberTypeID = T.PhoneNumberTypeID

,I have a few questions about. What does the N and T mean? Also, why an inner join?

Thank you so much for the help.

 

[PHV]

N et T are aliases (for clarity)
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dwAccessUser32]

Thanks I will look at this.