Record Source for Form issue

[moishier]

I have two tables: one with user records, and one with user complaints.

I have a form that you can enter complaints. In the form, I want to:

1. Be able to add new records via the form to the complaints table via the record navigation option.
2. Be able to load the user's name from the Users table by looking up the userid entered in the complain form.

I tried setting the record source for the form to a query that joined both tables, but then the form lost the ability to add new records.

What is the solution?

Thanks!

 

[Catalina]

Can a user have more than one complaint?

If so you need a table for the users with all the relevant data about those users and a separate table for the complaints.

Then you would use a main form for the users and a sub form on the main form for the complaints and link them on a common field, like a user id field.

Catalina

 

[Beetle]

Be able to load the user's name from the Users table by looking up the userid entered in the complain form.

What do you mean by "load the user's name"? You want to display it on your Complaints form? You can do that with a combo box.

If you mean something else, post back with more detail.

 

[moishier]

Yes, a user can have more than one complaint.

I want to do the reverse: From the form that loads all complaints and manages them, I want to simply display the name of the user that the complaint is about. The name data is located in another table than the one that the form is being sourced from.

 

[Catalina]

From the form that loads all complaints and manages them

It is not very clear what you mean by that,

I would probably make 2 forms.

The first one as a continuous form that lists all complaints with some of the most relevant fields. And a 2nd one that is the actual edit form of the complaint.
It is easy to make it so a click on a row in the continuous form takes you to the edit form and the selected record.

And use a combo box in the edit form to select the user, like Beetle suggested.

Catalina

 

[Beetle]

Then, like I said, use a combo box. The Control Source of the combo box would be the UserID from the record source of your form and the Row Source would return the UserID and the UserName from the Users table.

 

[moishier]

What do you mean by "load the user's name"? You want to display it on your Complaints form? You can do that with a combo box.

Yes, exactly what I want to do.

How can I do that with a combo box? I want to load the name data from another table, but there is no relationship defined between the tables in this form's source. When I define a relationship it makes the form not able to add new records.

 

[moishier]

Yes, exactly what I want to do.

How can I do that with a combo box? I want to load the name data from another table, but there is no relationship defined between the tables in this form's source. When I define a relationship it makes the form not able to add new records.

I got it to work with a combo box, thanks!