connecting form to a table?

I am relatively new to this and was wondering how to connect my form to a table?

Thanks,
MrLee


Post your answer or comment

comments powered by Disqus
Summary
I've searched this excellent forum for a possible solution but to no avail. In summary, I need to use the After Update event on a form combo box to pass data from a form to a table.

Detail
I have a simple Access 2007 database made up of two tables: tblUsers and tblPhoneNumbers.

In tblUsers, I have 3 fields: UserID (Autonumber / primary key), Username, PhoneNo
In tblPhoneNumbers, I have two fields: PhoneID (primary key), UserID

tblPhoneNumbers!PhoneID stores a list of all possible phone number extensions (eg 4001 through 4199).
In the tblUsers!PhoneNo field I can pick a phone number from the tblPhoneNumbers table using a combo box lookup.

I've created a query called qryPhoneNumbers that includes tblPhoneNumbers!PhoneID and tblUsers!Username, and that lists all phone numbers in column 1 and users in column 2. I've set the join type so I get to see all phone numbers, including numbers not yet assigned to a user. I use this as the source for the combo box lookup for the tblUsers!PhoneNo field.

I've created a query called qryMain which joins the tblUsers and the tblPhoneNumbers!PhoneNo field and tblPhoneNumbers!PhoneID together.

For data entry, I use a form (frmMain) based on qryMain. I use this form in datasheet view to assign phone numbers to users. The tblUsers!PhoneNo field uses a combo box with qryPhoneNumbers as it's record source.

Here's my problem:

Let's say I have 3 users (Alan, Bob and Charlie) in the tblUsers table and 3 phone extensions (4001, 4002 and 4003) in the tblPhoneNumbers table. No one has been assigned any numbers yet. In frmMain I move to Alan's record, and using the PhoneID combo box, I assign him 4001.

At this point, I need the After Update event to pass (store) Alan's UserID (say "1") from the current record in the form to the UserID field in the matching record (ie, 4001) in the tblPhoneNumbers table. Equally, if I delete 4001 from the form, the UserID value should be deleted from the corresponding UserID field in the tblPhoneNumbers table.

Very much appreciate any advice on this.

Thanks for reading.
Mark

I ask myself is it better to bind the form to a table to create new records, or change old ones (Single record form) or to use recordsets


though it's easier to bind the form to a table using recordsets give more options and freedom.
it's also give a good start if you want to move from Access for the front end application.

Hi All,

I need to bind fields using an expression in a form to a table.

My situation is as follows:

In one of my forms I have to enter the [Invoice Date]. Two other fields derive the [Budget Year] and [Quarter] from the Invoice Date by use of a expression placed in the Control Source box. For instance, =Switch(Format([Budget Date];"q")="1";"4";Format([Budget Date];"q")="2";"1";Format([Budget Date];"q")="3";"2";Format([Budget Date];"q")="4";"3")

Unfortunately the data entered in the fields isn't copied to my Budget Table (to which the form is linked). However, I did add a column [Budget Year] as well as [Quarter] to the [Budget] table. How can I make sure the entered data is copied to the Budget Table?

Regards,

Therese

Hi all, is there a way to link a expression (from a field in a form) to a data table? In my case I am trying to link a field named Budget Year to a field with the same name in my Budget Table. I placed the expression in the Control source (using the form properties window) box of the field Budget Year.

Regards,

Therese

I have a form that is based on a table and includes a field [Card ID] (also in the table) that is generated from the AutoNumber field [Customer ID]. Card ID = (Customer ID + 5555149600000000) when I enter the the data into the form and save it it saves everything to the table apart from the Card ID number that is generated by the form.

How do I get it to include the value in the Card ID field in the form to the table?

I've made a form which has a field which calculates the values of previously entered data.
The data should then be saved to a table called Form Results.
When the field(Combo37) is a combo box, and the control source is the Forms Results table, the data saves fine(the row source is the formula).
The drawback being the user must manually click on the combo box arrow to get the data to appear in the drop down and then it will save.
Anytime i make the field on the form a list box, the formula works, but the data does not save to the table .

Anysuggeations on how to easily solve saving the field in this form to a table?

Table=Form Results
Field in Form = Combo37 for reference.

I have a data entry form that contains 3 combo boxes and 2 list boxes. I want the selections to be saved to a table named "Classes_taken" (This is what I have set for my form record source). There are two tables used to populated the combo boxes and list boxes. They are "Class_Catalog" and "Emp". The combo boxes are cascading. Everything works fine except saving a record. After the user makes their choices and clicks the "new" record selector, only one field [LstBoxCDate] is saved to the table. Here are my controls:

Control Name Control Source Row Source
CboName SELECT Emp.Last_Name FROM Emp
ORDER BY Emp.Last_Name;

CboCName SELECT DISTINCT
Class_Catalog.Class_Name FROM
Class_Catalog ORDER BY
Class_Catalog.Class_Name;

LstBoxCDate Date_Taken SELECT Class_Catalog.Class_Date FROM
Class_Catalog WHERE
(((Class_Catalog.Class_Name)=[Forms]!
[entry]![CboCName]));

lstboxEmpID Emp_ID SELECT Emp.Emp_ID FROM Emp WHERE
(((Emp.Last_Name)=[Forms]![entry]!
[CboName]));

LstBoxCCode Class_Code SELECT DISTINCT
Class_Catalog.Class_Code FROM
Class_Catalog WHERE
(((Class_Catalog.Class_Name)=[Forms]![entry]!
[CboCName])) ORDER BY
Class_Catalog.Class_Code;

Thank you for your help. If you need

Hello,

We operate a food concession as a fund raiser once a year for a benefit.

I wish to have the cashier at the food concession check customres out with a form screen that contains each food item ( Hamburger), a left and right arrow button for incrementing and decrementing the number purchaesed, a calculation text field next to it with the subtotal of hamburgers purchased.

The amount is calculated, the money exchanged, and change is given.

The =Now is used to stamp the transaction.

How do I get all of these fields into a table so at the end of the day, we have a tally of all the purchases? From this table I want to build a query to extract how many of each item sold, and also get the total amount of each item sold so next year we know how much of that item to buy.

In summary - the cashier form transacts all purchases, and a table needs to store all the days transactions so queries can be run on the data. All calculated subvalues and such need to go from the form to the table.

thanks
bob

bob

Hi there, Im just knocking up a quick Access thing for work - moving data from a field in a form to a table.


Anybody got any ideas why I get this error? "Runtime error (13) - type mismatch"

We're using Windows 2000 and Access 2002...

Cheers



Option Compare Database

Option Explicit

Private Sub cmdSubmitProject_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("select * from tblProjects")

Dim strMessage As String
Dim intOptions As Integer
Dim bytChoice As Byte
Dim Cancel As Integer

strMessage = "Are you sure you wish to reserve this booking?"
intOptions = vbQuestion + vbOKCancel
bytChoice = MsgBox(strMessage, intOptions)


If lblUserID = "" Then
MsgBox ("You must enter a UserID against the project to continue")
If lblProjectName = "" Then
MsgBox ("You must enter a Project title to continue")
If lblDateStarted = "" Then
MsgBox ("You must enter a Project Start date to continue")
If lblProjectDescription = "" Then
MsgBox ("You must enter a Project Description to continue")

Else
rst.AddNew
rst!UserID = lblUserID
rst!ProjectName = lblProjectName
rst!DateStarted = lblDateStarted
rst!ProjectDescription = lblProjectDescription
rst.Update
rst.Close
MsgBox ("Project added to system.")
DoCmd.Close

End If
End If
End If
End If



' If bytChoice = vbOK Then
' DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
'
' If bytChoice = vbCancel Then
' FirstName.SetFocus ' Go back to Name field.
' Cancel = True ' Cancel saving the record.
' Else

Exit_SaveRecord_Click:
Exit Sub

Err_SaveRecord_Click:
MsgBox Err.Description
Resume Exit_SaveRecord_Click




End Sub

Perhaps this isn't the most appropriate method of verifying user identity, as someone could use an existing login on some server, or login with someone elses password, but I believe that shouldn't be an issue to deal with here. I've spent the past 8 hours poring over the boards, and can't quite find this particular scenario on the boards. It seems that either a Dcount, or a DLookup would be the most likely avenues to take, but I am unable to get an accurate result from my If Statements.

I've got my DB to query a current users login to the system, and would like to make some buttons visible for all users, and some buttons useable to just a specified list of users. If I hard-code the users names into the VBA codes for each action I am successful, but when I attempt to compare the textbox on the form to a table of allowable users I get a result that seems to be completely ignoring my if statement and showing everything to everyone. This is the function that is causing the problems:


	Code:
	Private Sub Form_Open(Cancel As Integer)
On Error GoTo Exit_Form_Open_Click

With DoCmd
    .Hourglass True
    .SetWarnings False
    .OpenQuery "qry Mar Geology Data"
    .OpenQuery "qry Apr Geology Data"
    .OpenQuery "qry Graphing Query"
      'These are update queries that I am currently running because my data
      'relies on some Excel sheets from another department that I import for
      'calculation purposes.
    .SetWarnings True
    .Hourglass False
End With

If DLookup("[Allowed Usernames]", "tbl Database Admins", "[Allowed Usernames] = " & [UserNameControl]) Then
     ' I have also tried various punctuation options based on examples on the
     'boards. The [Allowed Usernames] is the field with usernames in the table
     'called "tbl Database Admins", and [UserNameControl] is the textbox on
     'this form, which is generated via a module that queries the windows
     'username, in my case that is  which matches an entry in tbl
     'above.
   Me.Modify_Budget.Visible = True
   Me.Modify_Forecast.Visible = True
   Me.Modify_Calculation_Factors.Visible = True
   Me.Daily_Data_Entry.Visible = True
   Me.Email_Report.Visible = True
   Me.Email_Report_Text.Visible = True
   Me.Modify_Email_List.Visible = True
   Me.Database_Admin_List.Visible = True
Else
    Me.Modify_Budget.Visible = False
    Me.Modify_Forecast.Visible = False
    Me.Modify_Calculation_Factors.Visible = False
    Me.Daily_Data_Entry.Visible = False
    Me.Email_Report.Visible = False
    Me.Email_Report_Text.Visible = False
    Me.Modify_Email_List.Visible = False
    Me.Database_Admin_List.Visible = False
End If

Exit_Form_Open_Click:
    DoCmd.SetWarnings True
    DoCmd.Hourglass False
    Exit Sub

End Sub

I have attempted some error trapping within both if options, but the msgbox's that I put in did not appear at any point. It seems as if the condition of the if statement is as far as it goes, and the form will open with all options visible no matter who logs in, whether they are in the database admin list or not.

Hello,

This question may or may not make sense but i hope someone can answer it

Is it possible to print data using =[listbox].[column](0) in a form to a table?

So when a listbox is used in a form is it possible to print its data to a table?

Any suggestions would be welcome, thank you.

i am still very new to all this so please forgive me.

i have a form that gets data from a table and displays it within the form.

what i want it to do before it saves the new data over the original in the record, i want it to copy and paste 3 fields to a 2nd table.

here is the code for the save button ( it just saves the content of a text box on the form for the moment, i just want to see if i can get it to connect to the table and add a record )


	Code:
	Private Sub save_record_Click()
    ' conenction and recordset object variables
    Dim cn As Connection
    Dim rs As ADODB.Recordset
    Dim Datei As String
    
    Datei = currentdate
    
    ' open a connection to the connection object
    Set cn = CurrentProject.Connection
    ' initialise the recordset object
    Set rs = New ADODB.Recordset
    ' using the recordset object
    With rs
        .Open "history", cn, adOpenStatic, adLockPessimistic ' open it
        .AddNew ' prepare to add a new record
        .Fields("date") = "Datei"
        .Update ' update the table
        .Close ' close the recordset connection
    End With

End Sub

but it dosent work all i get is an error message:

Quote: Error accessing file. Network connection may have been lost. what am i doing wrong?

i have attached a copy of the DB if needed.

best regards

Hi,

I'm new to Access (2000) and have a question which is probably pretty basic.

I've written a form and database which I want to use for basic data entry. When the user clicks "OK", I simply want to append the data in the form to corresponding fields in a table. I guess I'm missing something obvious here because what I'm trying to do seems as though it should be fundamental in Access.

Is it done through a query, code, a macro or a combination of these? Any help and advice would be appreciated.

Thanks in advance,
Jon

Hi,

I currently have a database to store product information. In the database are two linked tables which are linked to a 3rd party suppliers database (one contains products one has prices). We are not allowed to edit this information in any way (hence it being linked), but we are allowed to copy the information from the 3rd party into our own table, and then edit it.

What I have created is a Query which takes the two linked forms to create a master list of products versus prices. I have then created a form based on this query output. What I want the end user to be able to do is look through the list of products on this form and when they get to the one they want, can hit a button which copies the information currently displayed on the form into the correct columns of our parts table.

So far I have had no luck in creating a macro to do this - any idea?

Thanks.

Ryan Cowie

Ok, I really need some help here. I have a table called tblServiceInfo and a form called frmServiceInfo. On both there is a field called Total Cost. What I have is a calculation on the form that figures out the total cost based on time and parts. what I need to do is get the total cost that appears on the form for that record to get put into the table. I need to know if this is possible and how to do it. Any help would be greatly appreciated!!

Hello,

I'm new to access, and I've been asked to analyze the structure of a database. In the Access Objects pane, I have a list of tables, queries, forms, reports, and one macro. I would like to know how to figure out which forms are connected to which queries. I figure that when the user hits submit on a form, it must execute some query which places the data filled into the form into a table. Is this right?

I'm using Access 2007.

Hi everyone I have created a form and a subform within it, I have managed to populate all the dropdown boxes from the tables I need. but I need to save all the information in putted to a table called bookingstbl and I do not know where to begin?

Regards

James

The problem:-

I have a form with a combo box with the values 1,2,3,4,5 in the drop down list.

I would like a user to select one of the numbers 1-5, lets say number 2. I would then like access to Add the corresponding number of records to a table ( in this case add 2 records )

Can this be done.

There is a field that links both the Form and the table together and that is Enquiry Ref.

Many thanks for any help

I have a form with several tabs on it, where the user enters various info.. I want to have the user hit the save button and have all info saved to a single table. Is there an easy way to do this. Please excuse my ignorance but I am fairly new to Access. Thanks in advance!

I am trying to use an update query to copy 4 fields from the current record on a form to a different table. A command button will execute the query, as well as other commands, via a macro.

"Edit Patient" is the form which the user uses to navigate the main table
"Print Template" is the table that I would like to copy the data to

Here's what I have so far (which updates absolutely nothing):

UPDATE [Print Template] SET [Print Template].[First Name] = [Forms]![Edit Patient]![First Name], [Print Template].[Last Name] = [Forms]![Edit Patient]![Last Name], [Print Template].[D/A] = [Forms]![Edit Patient]![D/A], [Print Template].DOB = [Forms]![Edit Patient]![DOB];


No criteria are necessary. If the command button is clicked, I want the update to occur.

Where am I going wrong? (novice user here, so be gentle)

is there an easy way to copy the current record on a form when clicking a command button such that a snapshot of that record is copied to a table for archiving purposes?

the table has about 120 columns so it is cumbersome to write an Insert Into sql statement within VBA code.

what i am attempting should be quite straightforward...i just want to take the current record in its entirety that is from a single table and append it to another table of the same structure.

any help would be appreciated!

This may be a very neebie question, but I have a form with drop downs for data choices. When an employee finishes with the selections, I would like all the data transferred to a table as a record. How would I go about doing this.

Any assistance is really appreciated!!

I'm certain this question has probably been asked before, but I can't seem to find it!

I have a form field called fldTitle, and want to ensure users write something that is meaningful by evaluating words within the title to a table of keywords (tblKeywords). I know how to write the IF/ENDIF and the other stuff required, but am struggling to find the right commands to do the comparision. Could someone help me out?

Many thanks.

Hi guys,
I´m a unexperienced user of access and it´s my first post in this forum. Hope to make myself clear.

I have several tables. My problem is rather simple but I didn´t find out to solve it. I searched on the forum but didn´t find it. I might not have the appropriate vocabulary yet to perform an efficient search.

My problem is that I have a form to fill a sales table (Salg). For each entry I need to fill the postnumber and the location. On another table (Poststed) is registered the Postnumber and the location name. On the form I did a combobox to get the post numbers. Then to get the location, In another box, which should come automatically after the "Postnr" has been selected, it should display the "location name". I did a "DLOOKUP" for that:

=DLookUp("[Poststed]";"Postnummer";"[Postnr] ='" & Skjemaer!Salg!Postnr & "'")

and it works fine. The name come automatically.
However, when I validate the "post" then all the data in the boxes are register in the destination table, but not the "location nam". It seems it is not bound to the table because the DLOOKUP function is in the source field.
Does anybody knows how I can bound it to the table.
Or if I should use another way to do the task, I´m open to any suggetions.
I hope I made myself clear enough.

Thanks for taking the time reading my post

Frederik


Not finding an answer? Try a Google search.